json data type - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for MySQL supports the JSON data type. This topic describes the format, usage notes, and examples of JSON data.

Usage notes

AnalyticDB for MySQL supports the standard JSON format. When writing JSON strings, you must strictly follow the standard JSON format specification.
Default values cannot be set for columns of the JSON data type.

JSON format requirements

AnalyticDB for MySQL has the following requirements for JSON data property key and property value:

Property keykey
Enclose the key in double quotation marks (""). For example, "addr" in {"addr":"xyz"}.
Property valuevalue
- The property value supports the following data types: BOOLEAN, NUMBER, VARCHAR, ARRAY, OBJECT, NULL.
  Note
  - When using JSON indexes, NUMBER cannot exceed the value range of DOUBLE.
  - ARRAY types can be plain arrays or nested arrays. For example, {"hobby":["basketball", "football"]} and {"addr":[{"city":"beijing", "no":0}, {"city":"shenzhen", "no":0}]}.
- If a value is of the STRING type, enclose the value in double quotation marks ("").
  Note
  If a value is a STRING and contains double quotation marks (""), you must add escape characters before the double quotation marks to indicate that they are part of the value. For example, if the value in {"addr":"xyz"ab"c"} is "xyz"ab"c", you would escape it as "xyz\"ab\"c". When writing data, backslashes (\) must also be escaped. Therefore, you would write {"addr":"xyz\\"ab\\"c"}.
- If value is a numeric type, you must write the data directly. Do not enclose value in double quotation marks ("").
- If value is a Boolean type, you must write true or false. Do not write 1 or 0. Both true and false must be lowercase.
- If value is Null, you must write Null directly.
- A key supports values of different types. Queries return results of the specified type.
  For example, executing the statement INSERT INTO test_tb1 VALUES ({"id": 1}) inserts the id value as the number 1. Executing the statement INSERT INTO test_tb1 VALUES ({"id": "1"}) inserts the id value as the string "1".
  If you execute the statement SELECT id FROM test_tb1 WHERE json_extract(col, '$.id')= 1; for a query, it returns the number "id": 1. If you execute the statement SELECT id FROM test_tb1 WHERE json_extract(col, '$.id')= '1'; for a query, it returns the string "id": "1".

Examples

Create a table

CREATE TABLE json_test(
  id int,
  vj json 
)
DISTRIBUTED BY HASH(id);

Write data

When writing data, JSON type fields are written in the same way as VARCHAR type fields. You must enclose the JSON string in single quotation marks. The following SQL examples show various JSON data 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 using the `json_extract` function

Syntax
```
json_extract(json, jsonpath)
```
Command description
Returns the value specified by jsonpath from the JSON.
Parameters
- json: The column name of the JSON column.
- jsonpath: The path of the JSON property key, separated by periods (.). $ represents the outermost path.
For more information about JSON functions, see JSON functions.

Examples

Basic query

SELECT json_extract(vj,'$.name') FROM json_test WHERE id=1;

Equality 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;
```
Note
When querying ARRAY data, you must use specified data subscripts to retrieve values. The ordinal number starts from 0 and increments. Iterating through the entire array is not supported.

Unnest JSON arrays using the `unnest` function

The kernel version of the cluster must be 3.2.5 or later.

Note

To view and update the minor version, go to the Configuration Information section on the Cluster Information page in the AnalyticDB for MySQL console.

Syntax
```
unnest(json_array)
```
Command description
Unnests a JSON array, making each element in the array a row in the result set.
Parameters
json_array: JSON data.

Example

SELECT * FROM unnest(json '[{"a":"123"},{"a":"456"}]');

Returned result

+-------------+
| _col0       |
+-------------+
| {"a":"123"} |
| {"a":"456"} |
+-------------+

References

JSON indexes