JSON Type

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

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Present starting in MariaDB Community Server 10.6.0.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.5 Community

  • Present starting in MariaDB Community Server 10.5.0.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

10.4 Community

  • Present starting in MariaDB Community Server 10.4.0.

10.3 Enterprise

  • Present starting in MariaDB Enterprise Server 10.3.16-1.

10.3 Community

  • Added in MariaDB Community Server 10.3.1.

10.2 Enterprise

  • Present starting in MariaDB Enterprise Server 10.2.25-1.

10.2 Community

  • Added in MariaDB Community Server 10.2.7.

EXTERNAL REFERENCES