Skip to content

JSON Data Type

MatrixOne supports native JSON data types defined by RFC 7159, allowing efficient access to data in JSON (JavaScript object notation) documents. Compared to storing strings in JSON format in string columns, JSON data types have the following advantages:

  • Automatically verify the JSON document stored in the JSON column. An invalid document will report an error.

  • Automatically optimize storage format. The JSON document stored in the JSON column is converted to an internal format that allows quick read access to the document elements. When the server has to read a JSON value stored in this binary format, it does not need to parse the value from the text. The binary format structure enables the server to find sub-objects or nested values ​​directly through key or array indexes.

The storage space required to store a JSON document is roughly the same as BLOB or TEXT.

Types of JSON

The JSON type has JSON arrays and JSON objects.

  • JSON arrays contain commas separated and included in the [] character, such as:
["abc", 10, null, true, false]
  • A JSON object contains a set of key-value pairs separated by commas and contained within the {} character, such as:
{"k1": "value", "k2": 10}

JSON arrays and objects can contain scalar values, i.e. strings or numbers, JSON empty literals, or JSON boolean true or false literals. The key value in the JSON object must be a string. JSON also allows time (date, datetime) scalar values. like:

["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

Nested in JSON array elements and JSON object key values, such as:

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

Normalization of JSON values

When a string is parsed into a valid JSON document, it is also normalized. This means that members of the key that repeats the keys found in the document, read from left to right, will be discarded. This means that when reading from left to right, the repeated key values ​​that appear later will be ignored.

Normalize when inserting values ​​into JSON columns, as follows:

CREATE TABLE t1 (c1 JSON);
INSERT INTO t1 VALUES
     ('{"x": 17, "x": "red"}'),
     ('{"x": 17, "x": "red", "x": [3, 5, 7]}');

mysql> SELECT c1 FROM t1;
+------------------------+
| c1 |
+------------------------+
| {"x": "red"} |
| {"x": [3, 5, 7]} |
+------------------------+
2 rows in set (0.01 sec)