JSON Type

Overview

A synonym of LONGTEXT with a default json_valid() CHECK (check added in MariaDB Community Server 10.4.3).

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.5 ES, and in 10.4 ES

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

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES