AnalyticDB:JSON

Chaves

Delimite cada chave com aspas duplas. Por exemplo, "addr" em {"addr":"xyz"}.

Valores

Um valor pode ser de um dos seguintes tipos: BOOLEAN, NUMBER, VARCHAR, ARRAY, OBJECT ou NULL.

BOOLEAN

Escreva true ou false em letras minúsculas. Não use 1 ou 0.

NUMBER

Informe valores numéricos diretamente, sem aspas.

VARCHAR (string)

Delimite valores de string com aspas duplas.

Se a string contiver aspas duplas, escape cada uma com uma barra invertida. Por exemplo, o valor xyz"ab"c é escrito como "xyz\"ab\"c". Barras invertidas também exigem escape; portanto, a entrada JSON completa torna-se {"addr":"xyz\\"ab\\"c"}.

ARRAY

Os arrays podem ser simples ou aninhados:

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

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

NULL

Escreva Null diretamente.

Consultas específicas por tipo

Uma mesma chave pode conter valores de tipos diferentes em linhas distintas. As consultas retornam resultados correspondentes ao tipo do valor usado na comparação.

Por exemplo:

• INSERT INTO test_tb1 VALUES ({"id": 1}) — armazena id como o número 1

• INSERT INTO test_tb1 VALUES ({"id": "1"}) — armazena id como a string "1"

Nas consultas:

• WHERE json_extract(col, '$.id') = 1 retorna apenas as linhas em que id é o número 1

• WHERE json_extract(col, '$.id') = '1' retorna apenas as linhas em que id é a string "1"

Crie uma tabela

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

Gravar dados

Grave em colunas JSON da mesma forma que em colunas VARCHAR: delimite a string JSON com aspas simples.

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"]}');

Consultar dados com json_extract

Sintaxe

json_extract(json, jsonpath)

Parâmetros

Retorna o valor especificado por jsonpath no JSON. Para outras funções, consulte Funções JSON.

Exemplos de consulta

Os exemplos abaixo usam a tabela json_test criada anteriormente.

Consulta básica — recuperar um único campo:

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

Consultas de igualdade:

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

Consultas de intervalo:

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

Verificações de NULL:

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;

Consultas IN:

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

Consultas LIKE:

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

Consultas de elementos de array — selecione elementos do array por índice (base zero):

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

Expandir arrays JSON com unnest

A função unnest expande um array JSON para que cada elemento se torne uma linha separada no conjunto de resultados. Requer versão de kernel do cluster 3.2.5 ou posterior.

Sintaxe

unnest(json_array)

Parâmetro

json_array: Um valor de array JSON.

Exemplo

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

Resultado:

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