MySQL JSON Versus MariaDB JSON and the Differences

MariaDB forked from MySQL, kept the name JSON for a column type and then implemented it in a completely different way. SQL that touches only the basics runs on both, and anything beyond the basics tends to fail with a syntax error or, worse, succeed with a different answer. This article lines the two up, using MySQL 8.4.11 and MariaDB 11.8.9 run side by side on the same 200,000 documents: how the data is stored, which operators and functions exist, how indexing works, what the errors look like, and how fast each one is.

What the type is

In MySQL, JSON is a real type. Text is parsed on write, rejected if invalid and stored in a binary form with an offset table that allows direct access to a key or element.

In MariaDB, JSON is an alias. show create table printed:

`doc` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`doc`))

So a MariaDB JSON column is a LONGTEXT column with a CHECK constraint. Every consequence follows from that:

• Text is kept exactly. I inserted {"b": 1, "a": [1, 2], "a": 3, "c": null}. MariaDB returned it unchanged, with the double space, the key order and both a keys. MySQL returned {"a": 3, "b": 1, "c": null}. If you must return what the client sent, MariaDB does that for free and MySQL cannot.

• Numbers are text. {"a": 12345678901234567890, "d": 1.50, "e": 1e2} came back byte for byte on MariaDB. MySQL turns 1.50 into 1.5 and 1e2 into 100.0.

• Invalid JSON is rejected by the check constraint, with a message about the constraint and no position: CONSTRAINT t.doc failed for jsonlab.t. MySQL says what is wrong and where: Invalid JSON text: "Missing a name for object member." at position 1. MySQL's message is much better for debugging (fixing invalid JSON text errors in MySQL inserts).

• The column can be converted to and from LONGTEXT without a change of meaning in MariaDB. In MySQL the conversion rewrites the data.

• Size. The 200,000-row table was 44.6 MB in MariaDB and 47.6 MB in MySQL. The binary format's offsets cost more than they save for small documents.

The CHECK constraint is also something you can drop or forget. If a table is created as longtext and the check is omitted, MariaDB will accept anything. Check show create table on tables that were migrated from other servers.

Syntax that exists in only one

I ran the same statements against both servers and recorded what failed:

The last two rows are the reverse of the usual pattern: MariaDB has some conveniences MySQL lacks. In MariaDB, json_arrayagg(json_value(attrs, '$.sku') order by id desc limit 2) returned ["SKU-000005","SKU-000004"] with ordering and a limit in one call, and json_arrayagg(distinct ...) returned the five distinct brands, sorted. The result is also compact (["a","b"], no spaces after commas), where MySQL pads with spaces.

A good portability check is whether a query works with only JSON_EXTRACT, JSON_VALUE, JSON_SET, JSON_CONTAINS, JSON_OBJECT and generated columns. All of those ran on both.

Same name, different result

The dangerous differences are the ones that do not error.

JSON null. json_unquote(json_extract('{"a": null}', '$.a')) returns the text null on both servers, and json_value('{"a": null}', '$.a') is null was 1 on both. The difference is ->>, which exists only on MySQL and returns the text null for a JSON null. A condition where doc ->> '$.x' is null therefore finds only missing keys on MySQL, while its port where json_value(doc, '$.x') is null also finds keys set to null, so the two return different rows.

Booleans. json_extract(attrs, '$.in_stock') printed true on MariaDB and json_value printed 1. In MySQL both forms exist with their own rules (JSON_EXTRACT and the arrow operators).

JSON_TABLE defaults. In MySQL the default is a JSON literal in a string, default '"none"' on empty. In MariaDB the same clause stored the quotes into a varchar(5) and produced "none. MariaDB takes a plain SQL string, default 'none' on empty. The bad-value behavior differs: a int path '$.a' over the string "abc" returned 0 in MariaDB in both the default and error on error forms, and NULL (or error 3156 with error on error) in MySQL (using JSON_TABLE in MySQL).

Collation. MariaDB's JSON column has utf8mb4_bin, but a generated column takes the column default, utf8mb4_uca1400_ai_ci. where sku = 'sku-123456' on the generated column matched the upper-case SKU and where json_value(attrs, '$.sku') = 'sku-123456' matched nothing. MySQL does the same with its collations, and also rewrites the JSON expression to use the generated column's index, which MariaDB did not do in my test: the explain for the json_value form showed type: ALL with 197,190 rows.

Array and number paths. $[-1] works on MariaDB; on MySQL it is an error (use $[last], which both accept).

Indexing

On MariaDB you index a virtual or persistent generated column: alter table products add column sku varchar(20) as (json_value(attrs, '$.sku')) virtual, add index idx_sku (sku) worked, and where sku = 'SKU-123456' used idx_sku with Using index. The expression-index forms that MySQL supports, create index ix on products ((json_value(attrs, '$.sku'))), and the array form, ((cast(d -> '$.a' as char(5) array))), are syntax errors. So there is no multi-valued index in MariaDB, and array searches always scan or go through a separate table. The MySQL side is described in indexing a MySQL JSON field with a generated column and searching values inside a MySQL JSON array.

Speed

Parsing text on every access costs more than reading a binary format. The same four queries on the same 200,000 documents:

query                                              MySQL 8.4    MariaDB 11.8
count where brand = 'Acme' (json_value)              62 ms         71 ms
group by brand, avg(price) (two json_value calls)    151 ms      1,362 ms
count where specs.dims.w = 20 (nested extract)        61 ms        184 ms
count where json_valid(attrs) (full parse)            44 ms        155 ms
count where tags contains 'gift'                     114 ms        132 ms

For a shallow key near the start of the document the two are close. For anything that walks deeper or touches several fields, MariaDB was 3 to 9 times slower, because it parses the text for each call. The gap would widen with larger documents, which a binary format with offsets handles better. If you choose MariaDB, extract the fields you query often into generated columns and avoid repeated extraction in one query. JSON_TABLE reads the document once, and parsing a document once for several columns is the way to avoid paying the price repeatedly.

Moving between them

From MySQL to MariaDB: replace -> and ->> with JSON_EXTRACT and JSON_VALUE (or JSON_UNQUOTE(JSON_EXTRACT())); replace MEMBER OF with JSON_CONTAINS; replace functional and multi-valued indexes with generated columns and normal indexes; move ORDER BY out of the aggregate if you are going the other way; re-test every JSON_TABLE. A dump taken with mysqldump from MySQL 8 may declare columns as json, which MariaDB accepts as an alias.

From MariaDB to MySQL: data that passed the CHECK is valid JSON, but MySQL will normalize it, reorder keys and collapse duplicates, and will reject documents with numbers that do not fit a double. Count the documents with duplicate keys before the move.

If your application must run on both, write the JSON access in a thin layer (a repository class or a set of views) that holds the dialect differences, and run tests against both servers.

When the two can share one schema

They can, if you keep to a conservative subset:

• A JSON column with a NOT NULL and no default expression (MariaDB's default syntax differs).

• Reads with JSON_EXTRACT and JSON_VALUE; writes with JSON_SET, JSON_REMOVE and JSON_MERGE_PATCH.

• Search with JSON_CONTAINS and JSON_SEARCH.

• Indexes only on generated columns, declared with an explicit type and collation.

• Documents small, and no reliance on key order, whitespace or duplicate keys.

• Output built in the application, or JSON_OBJECT and JSON_ARRAYAGG without ORDER BY or DISTINCT.

Beyond that, pick one server and use its strengths. MySQL gives you the faster binary format, the arrow operators and multi-valued indexes. MariaDB gives you exact text, an easy ORDER BY in aggregates and some extra functions. If you are using JSON as the main storage format of your data, choosing between a JSON column and a document database is the bigger question. The overview of the MySQL side is in using the JSON column type in MySQL. A document can be pretty printed in the browser with the JSON formatter.

The vendors document their implementations at the MySQL JSON data type page and the MariaDB JSON data type page.