Catégories :: Fonctions de données semi-structurées (Parsing)

PARSE_JSON¶

Interprète une chaîne d’entrée comme un document JSON, produisant une valeur VARIANT.

Voir aussi :: TRY_PARSE_JSON

Syntaxe¶

PARSE_JSON( <expr> )

Arguments¶

expr: Une expression de type chaîne (par exemple VARCHAR) qui contient des informations valides JSON.

Renvoie¶

La valeur renvoyée est de type VARIANT et contient un document JSON.

Notes sur l’utilisation¶

Cette fonction prend en charge une expression d’entrée avec une taille maximale de 8 MB compressée.
Si la fonction PARSE_JSON est appelée avec une chaîne vide, ou avec une chaîne ne contenant que des caractères d’espacement, la fonction renvoie NULL (au lieu de lancer une erreur), même si une chaîne vide n’est pas au format JSON valide. Cela permet de poursuivre le traitement plutôt que de l’interrompre si certaines entrées sont des chaînes vides.
Si l’entrée est NULL, la sortie est NULL aussi. Toutefois, si la chaîne entrée est 'null', alors elle est interprétée comme une valeur JSON nulle de sorte que le résultat ne soit pas SQL NULL, mais une valeur VARIANT valide contenant null. Un exemple est inclus dans la section Exemples ci-dessous.
Lors de l’analyse des nombres décimaux, PARSE_JSON tente de préserver l’exactitude de la représentation en considérant 123,45 comme NUMBER(5,2) et non comme DOUBLE. Toutefois, les nombres utilisant une notation scientifique (par exemple, 1,2345e+02) ou des nombres qui ne peuvent pas être stockés sous forme de nombres décimaux à virgule fixe en raison de limitations de plage ou d’échelle sont stockés sous la forme DOUBLE. Comme JSON ne représente pas des valeurs telles que TIMESTAMP, DATE, TIME ou BINARY de manière native, celles-ci doivent être représentées sous forme de chaînes.
En JSON, un objet (également appelé « dictionnaire » ou « hachage ») est un ensemble non ordonné de paires clé-valeur.

TO_JSON et PARSE_JSON sont des fonctions (presque) inverses ou réciproques.
- La fonction PARSE_JSON prend une chaîne en entrée et renvoie une VARIANT compatible avec JSON.
- La fonction TO_JSON utilise une VARIANT compatible avec JSON et renvoie une chaîne.
Conceptuellement, ce qui suit est vrai si X est une chaîne contenant du code JSON valide :

X = TO_JSON(PARSE_JSON(X));

Par exemple, ce qui suit est vrai (conceptuellement) :

'{"pi":3.14,"e":2.71}' = TO_JSON(PARSE_JSON('{"pi":3.14,"e":2.71}'))

Cependant, les fonctions ne sont pas parfaitement réciproques pour ces raisons :
- Les chaînes vides, et les chaînes ne contenant que des espaces, ne sont pas traitées réciproquement. Par exemple, la valeur de retour de PARSE_JSON('') est NULL, mais la valeur de retour de TO_JSON(NULL) n’est pas ''.
- L’ordre des paires clé-valeur dans la chaîne produite par TO_JSON n’est pas prévisible.
- La chaîne produite par TO_JSON peut avoir moins d’espaces que la chaîne transmise à PARSE_JSON.
Par exemple, les éléments suivants sont des équivalents JSON, mais pas des chaînes équivalentes :
- {"pi": 3.14, "e": 2.71}
- {"e":2.71,"pi":3.14}

Exemples¶

Cela montre un exemple de stockage de différents types de données dans une colonne VARIANT en appelant PARSE_JSON pour analyser des chaînes.

Créez et remplissez une table. Notez que l’instruction INSERT utilise la fonction PARSE_JSON.

create or replace table vartab (n number(2), v variant);

insert into vartab
    select column1 as n, parse_json(column2) as v
    from values (1, 'null'), 
                (2, null), 
                (3, 'true'),
                (4, '-17'), 
                (5, '123.12'), 
                (6, '1.912e2'),
                (7, '"Om ara pa ca na dhih"  '), 
                (8, '[-1, 12, 289, 2188, false,]'), 
                (9, '{ "x" : "abc", "y" : false, "z": 10} ') 
       AS vals;

Interroger les données :

select n, v, typeof(v)
    from vartab
    order by n;
+---+------------------------+------------+
| N | V                      | TYPEOF(V)  |
|---+------------------------+------------|
| 1 | null                   | NULL_VALUE |
| 2 | NULL                   | NULL       |
| 3 | true                   | BOOLEAN    |
| 4 | -17                    | INTEGER    |
| 5 | 123.12                 | DECIMAL    |
| 6 | 1.912000000000000e+02  | DOUBLE     |
| 7 | "Om ara pa ca na dhih" | VARCHAR    |
| 8 | [                      | ARRAY      |
|   |   -1,                  |            |
|   |   12,                  |            |
|   |   289,                 |            |
|   |   2188,                |            |
|   |   false,               |            |
|   |   undefined            |            |
|   | ]                      |            |
| 9 | {                      | OBJECT     |
|   |   "x": "abc",          |            |
|   |   "y": false,          |            |
|   |   "z": 10              |            |
|   | }                      |            |
+---+------------------------+------------+

L’exemple suivant montre le traitement de NULL pour PARSE_JSON et TO_JSON :

SELECT TO_JSON(NULL), TO_JSON('null'::VARIANT),
       PARSE_JSON(NULL), PARSE_JSON('null');
+---------------+--------------------------+------------------+--------------------+
| TO_JSON(NULL) | TO_JSON('NULL'::VARIANT) | PARSE_JSON(NULL) | PARSE_JSON('NULL') |
|---------------+--------------------------+------------------+--------------------|
| NULL          | "null"                   | NULL             | null               |
+---------------+--------------------------+------------------+--------------------+

Les exemples suivants illustrent la relation entre PARSE_JSON, TO_JSON et TO_VARIANT :

Créez une table et ajoutez des données VARCHAR, VARIANT génériques et VARIANT compatibles avec JSON. L’instruction INSERT insère une valeur VARCHAR et l’instruction UPDATE génère une valeur JSON qui correspond à ce VARCHAR.

CREATE or replace TABLE jdemo2 (varchar1 VARCHAR, variant1 VARIANT,
    variant2 VARIANT);
INSERT INTO jdemo2 (varchar1) VALUES ('{"PI":3.14}');
UPDATE jdemo2 SET variant1 = PARSE_JSON(varchar1);

Cette requête montre que TO_JSON et PARSE_JSON sont des fonctions conceptuellement réciproques :

SELECT varchar1, PARSE_JSON(varchar1), variant1,  TO_JSON(variant1),
                 PARSE_JSON(varchar1) = variant1, TO_JSON(variant1) = varchar1
    FROM jdemo2;
+-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+
| VARCHAR1    | PARSE_JSON(VARCHAR1) | VARIANT1     | TO_JSON(VARIANT1) | PARSE_JSON(VARCHAR1) = VARIANT1 | TO_JSON(VARIANT1) = VARCHAR1 |
|-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------|
| {"PI":3.14} | {                    | {            | {"PI":3.14}       | True                            | True                         |
|             |   "PI": 3.14         |   "PI": 3.14 |                   |                                 |                              |
|             | }                    | }            |                   |                                 |                              |
+-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+

Cependant, les fonctions ne sont pas exactement réciproques ; des différences dans les espaces ou l’ordre des paires clé-valeur peuvent empêcher la sortie de correspondre à l’entrée. Par exemple :

SELECT
    TO_JSON(PARSE_JSON('{"b":1,"a":2}')),
    TO_JSON(PARSE_JSON('{"b":1,"a":2}')) = '{"b":1,"a":2}',
    TO_JSON(PARSE_JSON('{"b":1,"a":2}')) = '{"a":2,"b":1}'
    ;
+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+
| TO_JSON(PARSE_JSON('{"B":1,"A":2}')) | TO_JSON(PARSE_JSON('{"B":1,"A":2}')) = '{"B":1,"A":2}' | TO_JSON(PARSE_JSON('{"B":1,"A":2}')) = '{"A":2,"B":1}' |
|--------------------------------------+--------------------------------------------------------+--------------------------------------------------------|
| {"a":2,"b":1}                        | False                                                  | True                                                   |
+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+

Bien que PARSE_JSON et TO_VARIANT puissent prendre une chaîne et renvoyer une variante, ils ne sont pas équivalents. Le code suivant utilise PARSE_JSON pour mettre à jour une colonne et TO_VARIANT pour mettre à jour l’autre colonne. (La mise à jour vers la colonne variant1 n’est pas nécessaire, car elle a déjà été mise à jour à l’aide d’un appel de fonction identique. Toutefois, le code ci-dessous le met à jour pour que vous puissiez voir côte à côte quelles fonctions sont appelées pour mettre à jour les colonnes).

UPDATE jdemo2 SET
    variant1 = PARSE_JSON(varchar1),
    variant2 = TO_VARIANT(varchar1);

La requête ci-dessous montre que la sortie de PARSE_JSON et la sortie de TO_VARIANT ne sont pas les mêmes. Outre les différences triviales d’espaces, il existe des différences plus importantes au niveau des guillemets.

SELECT variant1, variant2, variant1 = variant2 FROM jdemo2;
+--------------+-----------------+---------------------+
| VARIANT1     | VARIANT2        | VARIANT1 = VARIANT2 |
|--------------+-----------------+---------------------|
| {            | "{\"PI\":3.14}" | False               |
|   "PI": 3.14 |                 |                     |
| }            |                 |                     |
+--------------+-----------------+---------------------+