AnalyticDB for MySQL supports the JSON data type. This topic describes the format, usage notes, and examples of the JSON data type.
Usage notes
AnalyticDB for MySQL supports the standard JSON format. When you write JSON strings to a table, you must comply with the standard JSON format.
Default values cannot be set for columns of the JSON data type.
JSON format requirements
When you use JSON data in AnalyticDB for MySQL, take note of the following requirements on keys
and values
:
Keys
You must enclose a
key
in double quotation marks (""
). Example:"addr"
in{"addr":"xyz"}
.Values
The following data types are supported for a
value
: BOOLEAN, NUMBER, VARCHAR, ARRAY, OBJECT, and NULL.NoteA value of the NUMBER type in a JSON index must be within the allowed value range of the DOUBLE type.
AnalyticDB for MySQL allows you to write JSON arrays, including plain arrays and nested arrays. Examples:
{"hobby":["basketball", "football"]}
and{"addr":[{"city":"beijing", "no":0}, {"city":"shenzhen", "no":0}]}
.
If a
value
is of the STRING type, you must enclose thevalue
in double quotation marks (""
).NoteIf a
value
is of the STRING type and contains double quotation marks (""), you must add escape characters before the double quotation marks to indicate that the double quotation marks are part of thevalue
. For example, thevalue
in{"addr":"xyz"ab"c"}
is"xyz"ab"c"
. The value must be escaped as"xyz\"ab\"c"
. Backslashes (\
) are escaped when data is written. In this case, you must write{"addr":"xyz\\"ab\\"c"}
.If a
value
is of the NUMBER type, you can directly write thevalue
to the table instead of enclosing the value in double quotation marks (""
).If a
value
is of theBOOLEAN
type, you must writetrue
orfalse
instead of1
or0
to the table.true
andfalse
must be in lowercase.If a
value
isNULL
, you must writeNull
to the table.A
key
can havevalues
of different data types. The value of the data type that you specified is returned during queries.For example, if the
INSERT INTO test_tb1 VALUES ({"id":1})
statement is executed, the value ofid
is1
. If theINSERT INTO test_tb1 VALUES ({"id":"1"})
statement is executed, the value ofid
is"1"
.If the
SELECT id FROM test_tb1 WHERE json_extract(col, '$.id')= 1;
statement is executed,"id": 1
is returned. If theSELECT id FROM test_tb1 WHERE json_extract(col, '$.id')= '1';
statement is executed,"id": "1"
is returned.
Examples
Create a table
CREATE TABLE json_test(
id int,
vj json
)
DISTRIBUTED BY HASH(id);
Write data
Fields of the JSON type are written to tables in single quotation marks (''), in the same manner as fields of the VARCHAR type. The following SQL statements provide examples of different JSON formats:
INSERT INTO json_test VALUES(0, '{"id":0, "name":"abc", "age":0}');
INSERT INTO json_test VALUES(1, '{"id":1, "name":"abc", "age":10, "gender":"f"}');
INSERT INTO json_test VALUES(2, '{"id":3, "name":"xyz", "age":30, "company":{"name":"alibaba", "place":"hangzhou"}}');
INSERT INTO json_test VALUES(3, '{"id":5, "name":"a\\"b\\"c", "age":50, "company":{"name":"alibaba", "place":"america"}}');
INSERT INTO json_test VALUES(4, '{"a":1, "b":"abc-char", "c":true}');
INSERT INTO json_test VALUES(5, '{"uname":{"first":"lily", "last":"chen"}, "addr":[{"city":"beijing", "no":1}, {"city":"shenzhen", "no":0}], "age":10, "male":true, "like":"fish", "hobby":["basketball", "football"]}');
Query data
AnalyticDB for MySQL allows you to use the json_extract()
function to query data.
Syntax
json_extract(json, jsonpath)
Description
This function extracts one or more values specified by
jsonpath
from a valid JSON string.Parameters
json
: the name of the column of the JSON type.jsonpath
: the path of thekey
that is separated byperiods (.)
.$
specifies the outermost path.
For information about JSON functions, see JSON functions.
Examples
Basic query
SELECT json_extract(vj,'$.name') FROM json_test WHERE id=1;
Equivalent query
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') = 'abc'; SELECT id, vj FROM json_test WHERE json_extract(vj, '$.c') = true; SELECT id, vj FROM json_test WHERE json_extract(vj, '$.age') = 30; SELECT id, vj FROM json_test WHERE json_extract(vj, '$.company.name') = 'alibaba';
Range query
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.age') > 0; SELECT id, vj FROM json_test WHERE json_extract(vj, '$.age') < 100; SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') > 'a' and json_extract(vj, '$.name') < 'z';
IS NULL or IS NOT NULL query
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.remark') is null; SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') is not null;
IN query
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') in ('abc','xyz'); SELECT id, vj FROM json_test WHERE json_extract(vj, '$.age') in (10,20);
LIKE query
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') like 'ab%'; SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') like '%bc%'; SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') like '%bc';
ARRAY query
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.addr[0].city') = 'beijing' and json_extract(vj, '$.addr[1].no') = 0;
NoteWhen you query arrays, you can use subscripts of specific data. The subscript value starts from 0. You cannot iterate through an entire array.