AnalyticDB:JSON

Clés

Entourez chaque clé de guillemets doubles. Par exemple, "addr" dans {"addr":"xyz"}.

Valeurs

Une valeur peut être de l'un des types suivants : BOOLEAN, NUMBER, VARCHAR, ARRAY, OBJECT ou NULL.

BOOLEAN

Écrivez true ou false en minuscules. N'utilisez pas 1 ni 0.

NUMBER

Saisissez les valeurs numériques directement, sans guillemets.

VARCHAR (chaîne)

Entourez les valeurs de chaîne de guillemets doubles.

Si la chaîne contient des guillemets doubles, échappez chacun d'eux avec une barre oblique inverse. Par exemple, la valeur xyz"ab"c s'écrit "xyz\"ab\"c". Les barres obliques inverses doivent également être échappées, de sorte que l'entrée JSON complète devient {"addr":"xyz\\"ab\\"c"}.

ARRAY

Les tableaux peuvent être simples ou imbriqués :

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

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

NULL

Écrivez Null directement.

Interrogations spécifiques au type

Une clé peut contenir des valeurs de types différents selon les lignes. Les requêtes renvoient les résultats correspondant au type de la valeur de comparaison.

Par exemple :

• INSERT INTO test_tb1 VALUES ({"id": 1}) — stocke id sous forme du nombre 1

• INSERT INTO test_tb1 VALUES ({"id": "1"}) — stocke id sous forme de la chaîne "1"

Lors de l'interrogation :

• WHERE json_extract(col, '$.id') = 1 renvoie uniquement les lignes où id est le nombre 1

• WHERE json_extract(col, '$.id') = '1' renvoie uniquement les lignes où id est la chaîne "1"

Créer une table

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

Écrire des données

Les colonnes JSON s'écrivent de la même manière que les colonnes VARCHAR : entourez la chaîne JSON de guillemets 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"]}');

Interroger des données avec json_extract

Syntaxe

json_extract(json, jsonpath)

Paramètres

Renvoie la valeur spécifiée par jsonpath depuis le JSON. Pour plus de fonctions, consultez Fonctions JSON.

Exemples de requêtes

Tous les exemples ci-dessous s'exécutent sur la table json_test créée précédemment.

Requête de base — récupérer un seul champ :

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

Requêtes d'égalité :

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

Requêtes de plage :

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

Vérifications 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;

Requêtes 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);

Requêtes 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';

Interrogation d'éléments de tableau — accédez aux éléments du tableau par indice (base zéro) :

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

Développer les tableaux JSON avec unnest

La fonction unnest développe un tableau JSON afin que chaque élément devienne une ligne distincte dans l'ensemble de résultats. Nécessite la version 3.2.5 ou ultérieure du noyau du cluster.

Syntaxe

unnest(json_array)

Paramètre

json_array : Une valeur de tableau JSON.

Exemple

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

Résultat :

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