MariaDB: JSON datatype supported as of 10.2

Let’s say you have the following CREATE TABLE statement you wanted to run on a MariaDB instance.

CREATE TABLE `test` (
  `id` int unsigned not null auto_increment primary key,
  `value` json null
) default character set utf8mb4 collate utf8mb4_unicode_ci

You might be greeted with this error message;

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL
syntax; check the manual that corresponds to your MariaDB server version for the right
syntax to use near 'json null at line 1

Syntax-wise all is good, but the JSON data type is actually pretty new, and it’s only “supported” (these parentheses will become clear in a moment) as of MariaDB 10.2.

First of all: make sure you run the latest MariaDB.

MariaDB> select @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.0.31-MariaDB |
+-----------------+

If, like me, you were on an older release, upgrade that until you’re on MariaDB 10.2.

MariaDB> select @@version;
+----------------+
| @@version      |
+----------------+
| 10.2.7-MariaDB |
+----------------+

Then, be aware that MariaDB’s JSON implementation is slightly different than MySQL’s, as explained in MDEV-9144.

JSON data type directly contradicts SQL standard, that says, that JSON_* functions take a string as an argument.

Also, speed-wise MariaDB does not need binary JSON, according to our benchmarks, our JSON parser is as fast on text JSON as MySQL on binary JSON.

[…]

We’ll add JSON “type” for MySQL compatibility, though.

And as a final remark;

added JSON as an alias for TEXT

So behind the scenes, a JSON data type is actually a TEXT data type. But at least those CREATE TABLE queries will actually work.

That same CREATE TABLE statement above gets translated to this in MariaDB;

MariaDB> SHOW CREATE TABLE test;

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `value` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Note the TEXT datatype for the ‘value’ column, that I specified as JSON.