JSON Type

Overview

In 23.08 ES, 23.07 ES, 10.6 ES, 10.6 CS, 10.5 ES, 10.5 CS, 10.4 ES, 10.4 CS:

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

In 23.08 ES, 23.07 ES, 10.6 ES, 10.5 ES, 10.4 ES:

An alias 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. 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_EXTRACT().

USAGE

DETAILS

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

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`

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

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.

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

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