JSON Type
This page is part of MariaDB's Documentation.
The parent of this page is: Data Types for MariaDB Enterprise Server
Topics on this page:
Overview
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. This check is the default for MariaDB Server version 10.4.3 and beyond, but can also be manually added to any text field.
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_
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
EXTERNAL REFERENCES
Additional information on this topic may be found in the MariaDB Public Knowledge Base.