This topic describes how to use JSON in AnalyticDB for MySQL.
Background information
In the era of big data, a variety of solutions are available to retrieve structured data. However, most big data is semi-structured, and the amount of semi-structured data is increasing rapidly. Semi-structured data is much more difficult to understand and analyze than structured data. Mature solutions are required to process semi-structured data. AnalyticDB for MySQL provides JSON indexes as a semi-structured data retrieval feature to simplify semi-structured data processing.
Precautions
- If you set a specific column to a JSON data type during table creation, AnalyticDB for MySQL will automatically create a JSON index for the column, and this JSON index cannot be modified.
- JSON supports the following data types: BOOLEAN, NUMBER, VARCHAR, ARRAY, OBJECT, and NULL. A value of the NUMBER type must be within the allowed value range of the DOUBLE type. Otherwise, an error is returned.
- AnalyticDB for MySQL supports the JSON format. You must conform to the JSON syntax while entering JSON strings.
- You can only set the default value of newly created columns to NULL.
Create a table
In the following example, the vj
field is of the JSON type. After the table is created, AnalyticDB for MySQL automatically
creates a JSON index for the vj
column.
CREATE TABLE json_test(
id int,
vj json COMMENT 'The vj field is of the JSON type. AnalyticDB for MySQL automatically creates an index for this column.'
)
DISTRIBUTED BY HASH(id);
Limits
When you write JSON data into AnalyticDB for MySQL, take note of the following limits
on keys
and values
:
- key: You must enclose a
key
with double quotation marks (""
). - value
- If a
value
is of the STRING type, you must enclose thevalue
with double quotation marks (""
).If a
value
is of the STRING type and contains double quotation marks (""), you must add the escape character before the double quotation marks to indicate that it is part of thevalue
. For example, if avalue
is{"addr":"xyz"ab"c"}
, it must be escaped to{"addr":"xyz\"ab\"c"}
. - If a
value
is of the NUMBER type, you do not need to enclose the value with double quotation marks (""
). You can directly write thevalue
to AnalyticDB for MySQL. - If a
value
is of theBOOLEAN
type, you must writeTRUE
orFALSE
instead of1
or0
to AnalyticDB for MySQL. - If a
value
isNULL
, you must writeNULL
to AnalyticDB for MySQL.
- If a
- 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}]}
.
Write data into tables
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 that contain multiple examples of JSON data types are provided for your reference:
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":"femal"}');
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":"abc", "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 is used to return values specified by
jsonpath
injson
. - Parameters
json
: the name of the column of the JSON type.jsonpath
: the path of thekey
, which is separated by periods (.
).$
indicates the outermost path.
For more information about JSON functions, see JSON functions.
- Example
- 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 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; select id, vj from json_test where json_extract(vj, '$.addr[1].city') = 'shenzhen' and json_extract(json_test, '$.addr.no') = 0;
Note You can use subscripts when you query arrays. However, you cannot iterate through an entire array.
- Basic query
key
can correspond to different types of values
.
Examples: "key": 1
and "key": "1"
. json_extract(col, '$.key')=1
returns a number as shown in "key": 1
. json_extract(col, '$.key')='1'
returns a string as shown in "key": "1"
.