JSON Data Type

JSON is an alias for LONGTEXT introduced for compatibility reasons with MySQL's JSON data type. MariaDB implements this as a LONGTEXT rather, as the JSON data type contradicts the SQL standard, and MariaDB's benchmarks indicate that performance is at least equivalent.

In order to ensure that a a valid json document is inserted, the JSON_VALID function can be used as a CHECK constraint.

Examples

CREATE TABLE t (j JSON);

DESC t;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| j     | longtext | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

With validation:

CREATE TABLE t2 (
  j JSON 
  CHECK (JSON_VALID(j))
);

INSERT INTO t2 VALUES ('invalid');
ERROR 4025 (23000): CONSTRAINT `j` failed for `test`.`t2`

INSERT INTO t2 VALUES ('{"id": 1, "name": "Monty"}');
Query OK, 1 row affected (0.13 sec)

Replicating JSON data between MySQL and MariaDB

The JSON type in MySQL stores the JSON object in a compact form, not as LONGTEXT as in MySQL. This means that row based replication will not work for JSON types from MySQL to MariaDB.

There is a a few different ways to solve this:

• Use statement based replication
• Change the JSON filed to type TEXT in MySQL

Converting a MySQL TABLE with JSON fields to MariaDB

MariaDB can't directly access MySQL JSON format.

There is a a few different ways to move the table to MariaDB:

• Change the JSON filed to type TEXT in MySQL. After this MariaDB can directly use the table without any need of dump and restore.
• Use mysqldump to copy the table.

See also

• JSON Functions
• CONNECT JSON Table Type
• MDEV-9144