JSON Type
This page is part of MariaDB's Documentation.
The parent of this page is: Data Types for MariaDB Xpand
Topics on this page:
Overview
A native JSON data type that converts the assigned value into an internal JSON representation.
If an assigned value has a syntax error, it is rejected with an error.
If a dictionary value is specified with a duplicate key, the first value is the only one retained in the internal representation.
USAGE
JSON
DETAILS
Data Type | Maximum Size |
---|---|
|
|
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.
EXAMPLES
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'))
);