AnalyticDB:JSON

Keys

Enclose each key in double quotes. For example, "addr" in {"addr":"xyz"}.

Values

A value can be one of the following types: BOOLEAN, NUMBER, VARCHAR, ARRAY, OBJECT, or NULL.

BOOLEAN

Write true or false in lowercase. Do not use 1 or 0.

NUMBER

Write numeric values directly, without quotes.

VARCHAR (string)

Enclose string values in double quotes.

If the string contains double quotes, escape each one with a backslash. For example, the value xyz"ab"c is written as "xyz\"ab\"c". Backslashes must also be escaped, so the full JSON entry becomes {"addr":"xyz\\"ab\\"c"}.

ARRAY

Arrays can be simple or nested:

• Simple: {"hobby":["basketball", "football"]}

• Nested: {"addr":[{"city":"beijing", "no":0}, {"city":"shenzhen", "no":0}]}

NULL

Write Null directly.

Type-specific queries

A key can hold values of different types across rows. Queries return results that match the type of the comparison value.

For example:

• INSERT INTO test_tb1 VALUES ({"id": 1}) — stores id as the number 1

• INSERT INTO test_tb1 VALUES ({"id": "1"}) — stores id as the string "1"

When querying:

• WHERE json_extract(col, '$.id') = 1 returns only rows where id is the number 1

• WHERE json_extract(col, '$.id') = '1' returns only rows where id is the string "1"

Create a table

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

Write data

JSON columns are written the same way as VARCHAR columns: wrap the JSON string in single quotes.

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 with json_extract

Syntax

json_extract(json, jsonpath)

Parameters

Returns the value specified by jsonpath from the JSON. For more functions, see JSON functions.

Query examples

All examples below run against the json_test table created earlier.

Basic query — retrieve a single field:

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

Equality queries:

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 queries:

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';

NULL checks:

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 queries:

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 queries:

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 element queries — access array elements by subscript (zero-based):

SELECT id, vj FROM json_test WHERE json_extract(vj, '$.addr[0].city') = 'beijing' and json_extract(vj, '$.addr[1].no') = 0;

Expand JSON arrays with unnest

unnest expands a JSON array so that each element becomes a separate row in the result set. Requires cluster kernel version 3.2.5 or later.

Syntax

unnest(json_array)

Parameter

json_array: A JSON array value.

Example

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

Result:

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