La fonction GET_JSON_OBJECT extrait une valeur spécifiée d'un objet ou d'une chaîne JSON en fonction d'un chemin. Elle prend en charge plusieurs formats de données et règles d'échappement, et convient à l'analyse des données JSON dans MaxCompute. - MaxCompute

La fonction GET_JSON_OBJECT extrait une chaîne d'une chaîne JSON ou d'une valeur du type de données JSON en fonction d'un chemin JSON spécifié, json_path.

Syntaxe

STRING GET_JSON_OBJECT(JSON|STRING <json>, STRING <json_path>)

-- Example: Returns Alice.
SELECT GET_JSON_OBJECT(JSON '{"name": "Alice", "age": 30}', '$.name');

Notes d'utilisation

La fonction GET_JSON_OBJECT ne prend pas en charge la syntaxe des expressions régulières dans les chemins JSON.
La syntaxe du chemin JSON pour le nouveau type de données JSON diffère de la spécification d'origine. Cela peut entraîner des problèmes de compatibilité.
Si une requête contient plusieurs fonctions GET_JSON_OBJECT qui traitent les mêmes données JSON, la fonction analyse plusieurs fois la même chaîne JSON. Cela peut nuire aux performances et augmenter les coûts. Pour éviter cela, utilisez GET_JSON_OBJECT avec une fonction table définie par l'utilisateur (UDTF) pour transformer les données de journal JSON.

Paramètres

json : Obligatoire. Les données JSON à traiter. Ce paramètre prend en charge deux types d'entrée : JSON et STRING.
- Type JSON : Une valeur du type de données JSON. La valeur doit être au format {"Key":"Value", "Key":"Value",...}, par exemple JSON '{"name": "Alice", "age": 30}'.
- Type STRING : Si l'entrée est une chaîne STRING, elle doit respecter les exigences de format suivantes :
  - La chaîne doit être au format '{"Key":"Value", "Key":"Value",...}', par exemple '{"name": "Alice", "age": 30}'.
  - Échappez un guillemet double (") avec deux barres obliques inverses (\\).
  - Échappez un guillemet simple (') avec une barre oblique inverse (\).
json_path : Obligatoire. Une chaîne STRING qui spécifie l'expression de chemin JSON utilisée pour extraire les données. Le chemin doit commencer par un caractère $, par exemple $.aliyun.test[0].demo. L'expression de chemin utilise les caractères suivants :
- $ : Indique le nœud racine.
- . ou [''] : Indique un nœud enfant. Cela sert à analyser les objets JSON, par exemple $.store.book. Si une clé JSON contient un point (.), utilisez [''] à la place.
  
  L'extraction de données à l'aide de [''] n'est prise en charge que si vous exécutez l'instruction SET odps.sql.udf.getjsonobj.new=true;.
- [] : [number] indique un indice de tableau. L'indice commence à 0.
- * : Caractère générique pour []. Il renvoie l'intégralité du tableau. L'astérisque (*) ne peut pas être échappé.

Valeur de retour

Renvoie une valeur de type STRING. Cette valeur correspond aux données extraites du chemin spécifié. La fonction suit ces règles pour sa valeur de retour :

Si json est valide et que json_path existe, la chaîne correspondante est renvoyée.
Si json est vide ou a un format non valide, NULL est renvoyé.
Si json_path contient [*], la valeur de retour n'est pas au format tableau. Pour forcer la valeur de retour à être dans un format tableau unifié, exécutez l'instruction SET odps.sql.force.getjsonobj.array.format=true;.
Si json_path n'est pas valide, NULL est renvoyé.

Comportement de retour

Vous pouvez contrôler le comportement de retour de la fonction en définissant l'indicateur au niveau du projet ou de la session avec la commande suivante : SET odps.sql.udf.getjsonobj.new=true/false;.

Les deux comportements de retour correspondant aux différents paramètres d'indicateur sont les suivants :

Important

Nous vous recommandons d'utiliser la configuration SET odps.sql.udf.getjsonobj.new=true;. Cette configuration offre un comportement de fonction plus standard, simplifie le traitement des données et améliore les performances. Si votre projet MaxCompute comporte des tâches existantes qui dépendent du comportement d'échappement des caractères réservés JSON, nous vous recommandons de continuer à utiliser le comportement d'origine. Cela permet d'éviter les erreurs ou les problèmes d'exactitude qui pourraient survenir si vous basculez vers le nouveau comportement sans vérification.

Paramètres	`SET odps.sql.udf.getjsonobj.new=true;`	`SET odps.sql.udf.getjsonobj.new=false;`
Comportement de retour	Renvoie la chaîne d'origine sans modification.	Renvoie la chaîne avec les caractères réservés JSON échappés.
Comportement de retour	La valeur de retour est une chaîne JSON qui peut être analysée directement. Vous n'avez pas besoin d'utiliser des fonctions telles que `REPLACE` ou `REGEXP_REPLACE` pour remplacer les barres obliques inverses.	Les caractères réservés JSON, tels que les sauts de ligne (\n) et les guillemets ("), sont renvoyés sous forme de chaînes `'\n'` et `'\"'`.
Analyse des clés en double	Un objet JSON peut contenir des clés en double, qui peuvent être analysées avec succès. `-- Renvoie 1. SELECT GET_JSON_OBJECT('{"a":"1","a":"2"}', '$.a');`	Un objet JSON ne peut pas contenir de clés en double. S'il en contient, l'analyse peut échouer. `-- Renvoie NULL. SELECT GET_JSON_OBJECT('{"a":"1","a":"2"}', '$.a');`
Ordre de tri de sortie	La sortie est triée dans le même ordre que la chaîne JSON d'origine. `-- Renvoie {"b":"1","a":"2"}. SELECT GET_JSON_OBJECT('{"b":{"b":"1","a":"2"},"a":"2"}', '$.b');`	La sortie est triée par ordre alphabétique. `-- Renvoie {"a":"2","b":"1"}. SELECT GET_JSON_OBJECT('{"b":{"b":"1","a":"2"},"a":"2"}', '$.b');`

Si le mode de compatibilité Hive est activé en exécutant la commande SET odps.sql.hive.compatible=true;, la fonction GET_JSON_OBJECT conserve les chaînes d'origine dans sa valeur de retour.
Pour les projets MaxCompute créés le 21 janvier 2021 ou après, le comportement de retour par défaut de la fonction GET_JSON_OBJECT consiste à conserver les chaînes d'origine.
Pour les projets MaxCompute créés avant le 21 janvier 2021, le comportement de retour par défaut de la fonction GET_JSON_OBJECT consiste à échapper les caractères réservés JSON.
Utilisez l'exemple suivant pour déterminer le comportement utilisé par la fonction GET_JSON_OBJECT dans votre projet MaxCompute. Exécutez la commande suivante :
```
SELECT GET_JSON_OBJECT('{"a":"[\\"1\\"]"}', '$.a');
--The return value if the behavior is to escape JSON reserved characters:
[\"1\"]

--The return value if the behavior is to preserve original strings:
["1"]
```
Pour basculer le comportement de retour par défaut de la fonction GET_JSON_OBJECT dans votre projet afin de conserver les chaînes d'origine, soumettez un ticket. Cela évite d'avoir à définir la propriété au niveau de la session pour chaque session.

Exemples

Paramètre d'entrée JSON

Exemple 1 : Obtenir les valeurs de clés spécifiques à partir des données JSON

-- Returns 1.
SELECT GET_JSON_OBJECT(JSON '{"a":1, "b":2}', '$.a');

-- Returns NULL.
SELECT GET_JSON_OBJECT(JSON '{"a":1, "b":2}', '$.c');

Exemple 2 : Un paramètre json_path non valide renvoie NULL.

-- Returns NULL.
SELECT GET_JSON_OBJECT(JSON '{"a":1, "b":2}', '$invalid_json_path');

Paramètre d'entrée STRING

Exemple 1 : Extraire des informations de l'objet JSON src_json.json

-- Prepare the test data.
CREATE TABLE IF NOT EXISTS src_json (
    json STRING
);

INSERT OVERWRITE TABLE src_json
VALUES
('{"store":
  {"fruit":[{"weight":8,"type":"apple"},
    {"weight":9,"type":"pear"}],
     "bicycle":{"price":19.95,
     "color":"red"}},
   "email":"amy@only_for_json_udf_test.net",
   "owner":"amy"}');

-- Extract the information of the owner field. The return value is amy.
SELECT GET_JSON_OBJECT(src_json.json, '$.owner') FROM src_json;

-- Optional. Output by preserving the original string.
SET odps.sql.udf.getjsonobj.new=true;
-- Extract the information of the first array in the store.fruit field. The return value is {"weight":8,"type":"apple"}.
SELECT GET_JSON_OBJECT(src_json.json, '$.store.fruit[0]') FROM src_json;

-- Extract the information of a non-existent field. The return value is NULL.
SELECT GET_JSON_OBJECT(src_json.json, '$.non_exist_key') FROM src_json;

Exemple 2 : Extraire des informations des données de tableau JSON

-- Returns 2222.
SELECT GET_JSON_OBJECT('{"array":[["aaaa",1111],["bbbb",2222],["cccc",3333]]}','$.array[1][1]');

-- Output by preserving the original string.
SET odps.sql.udf.getjsonobj.new=true;
-- Returns ["h0","h1","h2"].
SELECT GET_JSON_OBJECT('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]');

-- Output by escaping JSON reserved characters.
SET odps.sql.udf.getjsonobj.new=false;
-- Returns ["h0","h1","h2"].
SELECT GET_JSON_OBJECT('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]');

-- Returns h1.
SELECT GET_JSON_OBJECT('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[1]');

Exemple 3 : Extraire des informations des données JSON contenant un point (.) dans la clé

-- Prepare the test data.
CREATE TABLE json_test (id STRING, json STRING);

-- Insert data where the key contains a period (.).
INSERT INTO TABLE json_test (id, json) VALUES 
("1", 
  "{
    \"China.beijing\":
      {\"school\":
        {\"id\":0,\"book\":
          [{\"title\": \"A\",\"price\": 8.95},
           {\"title\": \"B\",\"price\": 10.2}]
        }
      }
  }"
);

-- Insert data where the key does not contain a period (.).
INSERT INTO TABLE json_test (id, json) VALUES 
("2", 
  "{
    \"China_beijing\":
      {\"school\":
        {\"id\":0,\"book\":
          [{\"title\": \"A\",\"price\": 8.95},
           {\"title\": \"B\",\"price\": 10.2}]
        }
      }
  }"
);

-- Use square brackets [''] to parse data that contains a period (.).
-- This extracts the 'id' value under 'China.beijing'. The return value is 0.
SELECT GET_JSON_OBJECT(json, "$['China.beijing'].school['id']") FROM json_test WHERE id =1;

-- For data without special characters, both '.' and [''] are valid and equivalent.
-- This extracts the 'id' value under 'China_beijing'. The return value is 0.
SELECT GET_JSON_OBJECT(json, "$['China_beijing'].school['id']") FROM json_test WHERE id =2;
SELECT GET_JSON_OBJECT(json, "$.China_beijing.school['id']") FROM json_test WHERE id =2;

Exemple 4 : Utiliser `['']` pour les clés contenant un point (`.`)

SET odps.sql.udf.getjsonobj.new=true;

-- Returns 1.
SELECT GET_JSON_OBJECT('{"a.1":"1","a":"2"}', '$[\'a.1\']');

Exemple 5 : Entrée JSON vide ou non valide

-- Returns NULL.
SELECT GET_JSON_OBJECT('','$.array[1][1]');

-- Returns NULL.
SELECT GET_JSON_OBJECT('"array":["aaaa",1111],"bbbb":["cccc",3333]','$.array[1][1]');

Exemple 6 : Chaînes JSON échappées

SET odps.sql.udf.getjsonobj.new=true;

--Returns "1".
SELECT GET_JSON_OBJECT('{"a":"\\"1\\"","b":"2"}', '$.a'); 

--Returns '1'.
SELECT GET_JSON_OBJECT('{"a":"\'1\'","b":"2"}', '$.a');

Exemple 7 : Prise en charge des emojis

-- Returns the emoji symbol.
SELECT GET_JSON_OBJECT('{"a":"<Emoji symbol>"}', '$.a');

Remarque : DataWorks ne prend pas en charge la saisie directe de caractères emoji. Utilisez un outil tel que Data Integration pour écrire les chaînes encodées correspondant aux caractères emoji dans MaxCompute. Ensuite, utilisez la fonction GET_JSON_OBJECT pour les traiter.

Fonctions associées

Pour plus d'informations sur les fonctions associées, consultez Fonctions JSON.
Pour les bonnes pratiques, consultez Migrer des données JSON depuis OSS vers MaxCompute.
Pour plus d'informations sur json_path, consultez LanguageManual UDF.