JSON document storage. While often implemented as an alias for LONGTEXT, this type includes automatic validation to ensure valid JSON format.
The JSON alias was added to make it possible to use JSON columns in statement based replication from MySQL to MariaDB and to make it possible for MariaDB to read mysqldumps from MySQL.
JSON is an alias for LONGTEXT COLLATE utf8mb4_bin 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:2016 standard, and MariaDB's benchmarks indicate that performance is at least equivalent.
In order to ensure that a valid json document is inserted, the JSON_VALID function can be used as a CHECK constraint. This constraint is automatically included for types using the JSON alias.
The assigned text value is retained verbatim. If a value fails JSON_VALID(), an error is raised. This CHECK constraint can also be manually added to any LONGTEXT field. When a JSON object contains duplicate keys, only the first key-value pair is accessible via functions like JSON_EXTRACT().
With validation:
JSON example:
The JSON type in MySQL stores the JSON object in a compact form, not as as in MariaDB.
This means that row-based replication will not work for JSON types from MySQL to MariaDB.
There are a few different ways to solve this:
Use statement-based replication.
Change the JSON column to type TEXT in MySQL.
If you must use row-based replication and cannot change the MySQL master from JSON to TEXT, you can try to introduce an intermediate MySQL replica, and change the column type from JSON
MariaDB can't directly access MySQL's JSON format.
There are a few different ways to move the table to MariaDB:
From , you can use the plugin. See (blog post).
Change the JSON column to type TEXT in MySQL. After this, MariaDB can directly use the table without any need for a dump and restore.
.
In MySQL, JSON is an object and is . In MariaDB JSON strings are normal strings and compared as strings. One exception is when using in which case strings are unescaped before comparison.
This page is licensed: CC BY-SA / Gnu FDL
TEXTCREATE TABLE t (j JSON);
DESC t;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| j | longtext | YES | | NULL | |
+-------+----------+------+-----+---------+-------+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)CREATE TABLE json_example (
description VARCHAR(20),
example JSON
);SHOW CREATE TABLE json_example\G*************************** 1. row ***************************
Table: json_example
Create Table: CREATE TABLE `json_example` (
`description` varchar(20) DEFAULT NULL,
`example` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`example`))
) ENGINE=InnoDB DEFAULT CHARSET=latin1INSERT INTO json_example VALUES
('Array', '[ 1, 2, 3 ]'),
('Dictionary', '{ "a": 1, "b": 2 }'),
('Duplicates', '{ "a":1,"b":2, "a":3,"b": 4,"b":5}');SELECT * FROM json_example;+-------------+------------------------------------+
| description | example |
+-------------+------------------------------------+
| Array | [ 1, 2, 3 ] |
| Dictionary | { "a": 1, "b": 2 } |
| Duplicates | { "a":1,"b":2, "a":3,"b": 4,"b":5} |
+-------------+------------------------------------+SELECT description, JSON_EXTRACT(example, '$.b')
FROM json_example;+-------------+------------------------------+
| description | JSON_EXTRACT(example, '$.b') |
+-------------+------------------------------+
| Array | NULL |
| Dictionary | 2 |
| Duplicates | 2 |
+-------------+------------------------------+INSERT INTO json_example VALUES
('Invalid', '{');ERROR 4025 (23000): CONSTRAINT `json_example.example` failed for `test`.`json_example`