MariaDB JSON Is LONGTEXT in a Trench Coat: Production Implications

The migration looked effortless. MySQL 8.0 dump, load into MariaDB 10.11, every CREATE TABLE applies cleanly — JSON columns and all — and the team reasonably concludes that JSON just works on both sides. Six weeks later the slowest endpoints in the application are the JSON-heavy ones, the dumps do not diff cleanly against the old schemas, and somebody asks why a document equality check returns different rows than it did on MySQL. The answer is one sentence: in MariaDB, JSON is not a data type. It is an alias for LONGTEXT with a CHECK constraint, and everything surprising about it follows from that.

This post is what that means in production: what the alias expands to, the real storage and performance differences from MySQL's native binary JSON, the function and indexing toolbox (which is genuinely good), and the cross-compatibility traps when you replicate, dump, or migrate between the two engines.

What the alias expands to

MariaDB added the JSON keyword in 10.2.7, explicitly as a migration bridge for MySQL 5.7 schemas. When you write JSON in a CREATE TABLE, the parser rewrites it on the spot: the column becomes LONGTEXT with the utf8mb4 character set and the utf8mb4_bin collation, plus a CHECK constraint that calls JSON_VALID on the column. MySQL, by contrast, has a native binary type since 5.7: the document is parsed once at write time into a binary structure that supports random access to paths without re-reading the whole document.

CREATE TABLE events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  doc JSON NOT NULL,
  PRIMARY KEY (id)
);

SHOW CREATE TABLE events;

Run that SHOW CREATE TABLE and you will not find the word JSON anywhere in the output. You get a longtext column with a binary collation and a CHECK (json_valid(doc)) constraint. The alias exists so MySQL-shaped DDL loads without edits — and it does its job so quietly that plenty of teams never learn the substitution happened. One more nuance worth knowing: the validation exists only because you used the alias. A plain LONGTEXT column accepts any bytes, valid JSON or not; the CHECK constraint is the entire type system here.

Storage and performance: where the difference bites

Reads first. Every JSON_EXTRACT on MariaDB parses the document text from scratch. On a two-kilobyte document you will never feel it. On a two-hundred-kilobyte document, in a query that touches fifty thousand rows, you have just parsed gigabytes of JSON to answer one query — and it shows up as pure CPU with no lock waits, no disk stalls, nothing in the usual suspects. I have chased that mystery load twice; both times it was JSON path extraction in a reporting query.

Writes second. JSON_SET on MariaDB rewrites the whole document: read the text, modify it, store the new text in full. MySQL 8.0 can perform partial in-place updates of JSON columns when the update qualifies. In MariaDB, a hot row whose JSON blob grows by appends — an accumulating event log is the classic anti-pattern — becomes a write-amplification machine, and since the column is a LONGTEXT, large rewrites churn the redo log and the binlog in proportion to the document size, not the change size.

The practical rules fall out of the mechanics: keep documents small — kilobytes, not megabytes; never use a JSON column as an append-only log inside a hot row; index the paths you filter on instead of extracting them at query time; and promote genuinely hot scalar fields into real typed columns, where they get real statistics and real indexes.

Functions, JSON_TABLE, and indexing the paths you filter on

The function library is real and mostly MySQL-shaped: JSON_EXTRACT and JSON_UNQUOTE for paths, JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE for edits, JSON_LENGTH and JSON_KEYS for structure, JSON_VALUE (there since 10.2) for scalar extraction in one call — document and path only, no default clause — JSON_ARRAYAGG and JSON_OBJECTAGG for building documents from rows, and JSON_TABLE (10.6 and later) — the one I reach for most — which turns a JSON array into a relational rowset you can join and aggregate like a table. JSON_TABLE is the correct answer when reporting needs to treat array elements as rows; it is the difference between SQL and string surgery.

You cannot index inside a document. You index a generated column that extracts the path, and InnoDB happily builds a secondary index on a VIRTUAL generated column:

ALTER TABLE events
  ADD COLUMN user_email VARCHAR(254)
    GENERATED ALWAYS AS
      (JSON_UNQUOTE(JSON_EXTRACT(doc, '$.user.email'))) VIRTUAL,
  ADD INDEX idx_events_email (user_email);

-- JSON_TABLE: explode a JSON array into joinable rows (MariaDB 10.6+)
SELECT o.id, jt.sku, jt.qty
FROM orders o
JOIN JSON_TABLE(o.doc, '$.items[*]' COLUMNS (
       sku VARCHAR(40) PATH '$.sku',
       qty INT PATH '$.qty'
     )) AS jt;

For a filter that runs per request, that generated column plus index is the difference between a B-tree lookup and a full scan with a JSON parse per row. VIRTUAL columns are computed on read and cost no storage; PERSISTENT materializes them and trades disk for read speed. Either flavor can be indexed in InnoDB.

Cross-compat gotchas between MariaDB and MySQL

Equality is the quiet one. MariaDB compares JSON as binary-collation text: whitespace and key order are significant. MySQL compares normalized values — the document written as a-then-b equals the document written as b-then-a with extra spaces on MySQL, and not on MariaDB. Code that tests whole-document equality can change behavior across a migration without a single row changing. Filter on paths and scalars, not on document equality.

Validity is the second. MySQL's native type refuses invalid JSON at write time, always. MariaDB refuses it only where the alias installed its CHECK; a legacy LONGTEXT that you ALTER to JSON will fail the ALTER the moment it meets an invalid legacy row — clean the data first, then convert.

Dumps and diffs are the daily friction. mariadb-dump emits these columns as longtext plus a CHECK constraint, while MySQL tooling expects a native json type; schema-diff tools and ORM migration generators will report phantom differences forever unless you pick one engine's DDL as canonical and normalize at import. Replication deserves the most caution, and here the folklore is also wrong: row-based replication of MySQL's binary JSON into MariaDB does not work at all — the on-disk formats are physically incompatible, and MariaDB's own docs say so plainly. The alias was built for statement-based replication and for reading MySQL dumps; those paths work. If you are stuck with row-based binlogging, the documented escapes are converting the column to TEXT on the MySQL side, or chaining through an intermediate MySQL replica whose copy of the column is TEXT and replicating from there. Whatever the bridge, validate with JSON_VALID on arrival.

Finally, the edges of the function library diverge, though less than they used to: MariaDB's verbose structural dump is JSON_DETAILED, and since 10.6.12 (and the matching patch releases on the later branches) it also answers to MySQL's name for it, JSON_PRETTY. What MySQL still keeps to itself is the path operators and the JSON-aware optimizer machinery. Port the query patterns, not just the schema.

When the difference does not matter

Read-mostly configuration blobs, small documents, indexed access paths, moderate write rates: the alias is fine, and I run it in production without drama. Every failure I have seen came from treating a text column like a document database — megabyte blobs, per-row path parsing in analytics, hot in-place growth. MariaDB is at least honest about what JSON is; the alias is a bridge, and the CHECK constraint is the most valuable part of the deal.

Watching JSON workloads, and where MonPG is headed

JSON abuse never pages you directly. It shows up as a CPU bill nobody can explain and a reporting query that got slower every week for six weeks. The tells worth watching are query families whose CPU per row climbs with document size, generated-column indexes that go unused after a deploy, and table growth dominated by one LONGTEXT column. On tooling I will be exact: MonPG monitors PostgreSQL today, not MariaDB — MariaDB monitoring is coming soon and in active development, and the field notes in this series are what it is being built on. PostgreSQL went through its own version of this story with json versus jsonb, and the lesson — know what the storage format actually is — is the same. If that side of your fleet needs watching now, start with the PostgreSQL overview, see how the engines compare, or read on at the blog.