JSON Type

Functions

Xpand supports the following functions for the JSON data type:

• JSON_ARRAY()

• JSON_CONTAINS_PATH()

• JSON_DEPTH()

• JSON_EXTRACT()

• JSON_KEYS()

• JSON_LENGTH()

• JSON_OBJECT()

• JSON_QUOTE()

• JSON_SEARCH()

• JSON_TYPE()

• JSON_UNQUOTE()

• JSON_VALID()

Xpand does not support the following functions for the JSON data type:

• JSON_ARRAY_APPEND()

• JSON_ARRAY_INSERT()

• JSON_INSERT()

• JSON_MERGE()

• JSON_REMOVE()

• JSON_REPLACE()

• JSON_SET()

• JSON_CONTAINS()

For information about functions that are supported, see "Functions for MariaDB Xpand".

Operators

Xpand supports the following operators for the JSON data type:

• -> (column path operator)

• ->> (inline path operator)

For information about operators that are supported, see "SQL Operators for MariaDB Xpand".

Replication

Due to some of the replication differences in Xpand, if you are replicating from Xpand to MySQL or MariaDB Enterprise Server using statement-based replication, you may encounter errors.

Timestamps

Xpand stores timestamps inside JSON as DATETIME. This behavior differs from MySQL, which stores timestamps inside JSON as TIMESTAMP. Consequently, the JSON_TYPE() function returns a different result on Xpand and MySQL.

Xpand allows the full range of TIME values to be used. This behavior differs from MySQL, which sometimes limits the size of TIME values embedded in a JSON to 32 hours.

JSON_QUOTE() and JSON_UNQUOTE()

Xpand converts input to JSON_QUOTE() and JSON_UNQUOTE() to strings. This behavior differs from MySQL, which only accepts string input.

Comparisons and Sorting

Xpand uses the same ordering for both the comparison operators and ORDER BY. This behavior differs from MySQL and MariaDB Enterprise Server, which use different orderings for comparisons and ORDER BY.

Search and Ordering

Xpand performs a depth first search for JSON_EXTRACT() and JSON_SEARCH(). This behavior matches MariaDB Enterprise Server, but differs from MySQL, which performs a breadth first search. The difference can cause results to have a different order.

JSON

Example of JSON:

CREATE TABLE json_example (
   description VARCHAR(20),
   example JSON
);

Duplicate Key Handling

If a JSON object contains a duplicate key, the first value is the only one retained in the internal representation:

INSERT INTO json_example VALUES
   ('Array', '[ 1, 2, 3 ]'),
   ('Dictionary', '{ "a": 1, "b": 2 }'),
   ('Unduplicated', '{ "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} |
| Unduplicated | {"a": 1, "b": 2} |
+--------------+------------------+

Extract a Field

To extract a field from a JSON object, use the JSON_EXTRACT() function:

SELECT description, JSON_EXTRACT(example, '$.b') FROM json_example;

+--------------+------------------------------+
| description  | JSON_EXTRACT(example, '$.b') |
+--------------+------------------------------+
| Array        | NULL                         |
| Dictionary   | 2                            |
| Unduplicated | 2                            |
+--------------+------------------------------+

Syntax Validation

If invalid JSON is inserted into a JSON column, the value is rejected with a parse error:

INSERT INTO json_example VALUES ('Invalid', '{');

ERROR 3140 (HY000): [1071] Invalid JSON: "parse error: premature EOF
                                           {
                                        ^
"

Binary Storage

Xpand stores JSON columns in a binary format. Consequently, Xpand does not store JSON objects exactly as they are provided. For example, extra spaces are not stored:

TRUNCATE json_example;
INSERT INTO json_example VALUES ('Excessive spaces', '  {   "a": "a_val"   }  ');
SELECT * FROM json_example;

+------------------+----------------+
| description      | example        |
+------------------+----------------+
| Excessive spaces | {"a": "a_val"} |
+------------------+----------------+

Multiple JSON Objects

A JSON column can only store one JSON object. If multiple JSON objects are specified, the value is rejected with a parse error:

INSERT INTO json_example VALUES ('Two objects', '{} {}');

ERROR 3140 (HY000): [1071] Invalid JSON: "parse error: after key and value, inside map, I expect ',' or '}'
                                     {} {}
                                        ^
"

Empty String

A JSON column can't be set to an empty string. If an empty string is specified, the value is rejected with a parse error:

INSERT INTO json_example VALUES ('Empty string', '');

ERROR 3140 (HY000): [1071] Invalid JSON: "parse error: unallowed token at this point in JSON text

                                        ^
"

Indexes

Xpand supports secondary indexes on JSON columns.

CREATE TABLE t1 (
   id BIGINT(0) UNSIGNED PRIMARY KEY AUTO_UNIQUE,
   doc JSON,
   INDEX json_index (doc)
);

Columnar indexes are not supported for JSON columns.

Generated Columns

Generated columns can be used to return a subset of a JSON object:

CREATE TABLE t2 (
   id BIGINT(0) UNSIGNED PRIMARY KEY AUTO_UNIQUE,
   doc JSON,
   name_object JSON GENERATED ALWAYS AS (JSON_EXTRACT(doc, '$.name'))
);