:JSON

Claves

Escriba cada clave entre comillas dobles. Por ejemplo, "addr" en {"addr":"xyz"}.

Valores

Un valor puede ser de uno de los siguientes tipos: BOOLEAN, NUMBER, VARCHAR, ARRAY, OBJECT o NULL.

BOOLEAN

Escriba true o false en minúsculas. No utilice 1 ni 0.

NUMBER

Escriba los valores numéricos directamente, sin comillas.

VARCHAR (cadena)

Escriba los valores de cadena entre comillas dobles.

Si la cadena contiene comillas dobles, escape cada una con una barra invertida. Por ejemplo, el valor xyz"ab"c se escribe como "xyz\"ab\"c". Las barras invertidas también deben escaparse, por lo que la entrada JSON completa queda como {"addr":"xyz\\"ab\\"c"}.

ARRAY

Los arreglos pueden ser simples o anidados:

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

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

NULL

Escriba Null directamente.

Consultas específicas por tipo

Una clave puede contener valores de distintos tipos en diferentes filas. Las consultas devuelven resultados que coinciden con el tipo del valor de comparación.

Por ejemplo:

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

• INSERT INTO test_tb1 VALUES ({"id": "1"}) — almacena id como la cadena "1"

Al consultar:

• WHERE json_extract(col, '$.id') = 1 devuelve solo las filas donde id es el número 1

• WHERE json_extract(col, '$.id') = '1' devuelve solo las filas donde id es la cadena "1"

Crear una tabla

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

Escribir datos

Las columnas JSON se escriben de la misma manera que las columnas VARCHAR: encierre la cadena JSON entre comillas 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 datos con json_extract

Sintaxis

json_extract(json, jsonpath)

Parámetros

Devuelve el valor especificado por jsonpath desde el JSON. Para conocer más funciones, consulte JSON functions.

Ejemplos de consulta

Todos los ejemplos siguientes se ejecutan sobre la tabla json_test creada anteriormente.

Consulta básica: recuperación de un solo campo:

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

Consultas de igualdad:

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

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

Verificaciones 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 arreglo: acceso a elementos del arreglo mediante subíndice (base cero):

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

Expandir arreglos JSON con unnest

unnest expande un arreglo JSON de modo que cada elemento se convierte en una fila independiente en el conjunto de resultados. Se requiere la versión 3.2.5 o posterior del kernel del clúster.

Sintaxis

unnest(json_array)

Parámetro

json_array: Un valor de arreglo JSON.

Ejemplo

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

Resultado:

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