JSON Type
This page is part of MariaDB's Enterprise Documentation.
The parent of this page is: Data Types
Topics on this page:
Overview
- In 10.6 ES, 10.6 CS, 10.5 ES, 10.5 CS, 10.4 ES, 10.4 CS:
A synonym of LONGTEXT with a default
json_valid()
CHECK (check added in MariaDB Community Server 10.4.3).- In 10.3 ES, 10.3 CS, 10.2 ES, 10.2 CS:
See LONGTEXT.
The assigned text value is retained verbatim in the field. If an assigned value has a syntax error, it is rejected with an error if the field has a CHECK (JSON_VALID(field_name))
clause (which can be manually added if it was not added for you).
If a dictionary value is specified with a duplicate key, the first value is the only one that can be accessed via the functions that interpret the meaning of the data, such as JSON_EXTRACT().
See also: Data Types in 10.6 ES, in 10.6 CS, in 10.5 ES, in 10.5 CS, in 10.4 ES, in 10.4 CS, in 10.3 ES, in 10.3 CS, in 10.2 ES, and in 10.2 CS
EXAMPLES
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=latin1
INSERT 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`
CHANGE HISTORY
Release Series | History |
---|---|
10.6 Enterprise |
|
10.6 Community |
|
10.5 Enterprise |
|
10.5 Community |
|
10.4 Enterprise |
|
10.4 Community |
|
10.3 Enterprise |
|
10.3 Community |
|
10.2 Enterprise |
|
10.2 Community |
|
EXTERNAL REFERENCES
Additional information on this topic may be found in the MariaDB Public Knowledge Base.
Information specific to MariaDB SkySQL can be found on the JSON page in the SkySQL Documentation.