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

PARSE_JSON¶

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

Vous pouvez utiliser la fonction PARSE_JSON lorsque vous avez des données d’entrée au format JSON. Cette fonction peut convertir les données du format JSON en données ARRAY ou OBJECT et stocker ces données directement dans une valeur VARIANT. Vous pouvez ensuite analyser ou manipuler les données.

Par défaut, la fonction n’autorise pas les clés en double dans l’objet JSON, mais vous pouvez définir l’argument 'parameter' pour autoriser les clés en double.

Voir aussi :: TRY_PARSE_JSON

Syntaxe¶

PARSE_JSON( <expr> [ , '<parameter>' ] )

Copy

Arguments¶

Obligatoire :

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

Facultatif :

'parameter'

Constante de chaîne qui spécifie le paramètre utilisé pour rechercher les correspondances. Valeurs prises en charge :

Paramètre	Description
`d`	Autoriser les clés en double dans les objets JSON. Si un objet JSON contient une clé en double, l’objet renvoyé contient une instance unique de cette clé avec la dernière valeur spécifiée pour cette clé.
`s`	Ne pas autoriser les clés en double dans les objets JSON (strict). Cette valeur est la valeur par défaut.

Renvoie¶

Renvoie une valeur de type VARIANT qui contient un document JSON.

Si l’entrée est NULL, la fonction renvoie NULL.

Cette fonction ne renvoie pas de type structuré.

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 déclencher une erreur), même si une chaîne vide n’est pas au format JSON valide. Ce comportement 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. Voir les exemples ci-dessous.
Lors de l’analyse de 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 valeur DOUBLE. Toutefois, les nombres utilisant une notation scientifique (à savoir 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 d’une valeur DOUBLE. Comme JSON ne représente pas les valeurs telles que TIMESTAMP, DATE, TIME ou BINARY de manière native, ces valeurs 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) est NULL, et non pas la réciproque ''.
- 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¶

Les exemples suivants utilisent la fonction PARSE_JSON.

Stockage de valeurs de différents types de données dans une colonne VARIANT¶

Cet exemple montre le 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. L’instruction INSERT utilise PARSE_JSON pour insérer les valeurs VARIANT dans la colonne v de la table.

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;

Copy

Interroger les données. La requête utilise la fonction TYPEOF pour afficher les types de données des valeurs stockées dans les valeurs VARIANT.

SELECT n, v, TYPEOF(v)
  FROM vartab
  ORDER BY n;

Copy

+---+------------------------+------------+
| 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              |            |
|   | }                      |            |
+---+------------------------+------------+

Insérer un objet JSON avec des clés en double dans une valeur VARIANT¶

Tenter d’insérer un objet JSON avec des clés en double dans une valeur VARIANT :

INSERT INTO vartab
SELECT column1 AS n, PARSE_JSON(column2) AS v
  FROM VALUES (10, '{ "a" : "123", "b" : "456", "a": "789"} ')
     AS vals;

Copy

Une erreur est renvoyée car les clés en double ne sont pas autorisées par défaut :

100069 (22P02): Error parsing JSON: duplicate object attribute "a", pos 31

Insérer un objet JSON avec des clés en double dans une valeur VARIANT, et spécifier le paramètre d pour autoriser les doublons :

INSERT INTO vartab
SELECT column1 AS n, PARSE_JSON(column2, 'd') AS v
  FROM VALUES (10, '{ "a" : "123", "b" : "456", "a": "789"} ')
     AS vals;

Copy

+-------------------------+
| number of rows inserted |
|-------------------------|
|                       1 |
+-------------------------+

Une requête sur la table montre que seule la valeur de la dernière clé dupliquée a été insérée :

SELECT v
  FROM vartab
  WHERE n = 10;

Copy

+---------------+
| V             |
|---------------|
| {             |
|   "a": "789", |
|   "b": "456"  |
| }             |
+---------------+

Traitement des valeurs NULL avec les fonctions PARSE_JSON et TO_JSON¶

L’exemple suivant montre comment PARSE_JSON et TO_JSON traitent les valeurs NULL :

SELECT TO_JSON(NULL), TO_JSON('null'::VARIANT),
       PARSE_JSON(NULL), PARSE_JSON('null');

Copy

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

Comparaison de PARSE_JSON avec TO_JSON¶

Les exemples suivants illustrent la relation entre les fonctions PARSE_JSON et TO_JSON.

Cet exemple crée une table avec une colonne VARCHAR et une colonne VARIANT. L’instruction INSERT insère une valeur VARCHAR et l’instruction UPDATE génère une valeur JSON qui correspond à cette valeur VARCHAR.

CREATE OR REPLACE TABLE jdemo2 (
  varchar1 VARCHAR, 
  variant1 VARIANT);

INSERT INTO jdemo2 (varchar1) VALUES ('{"PI":3.14}');

UPDATE jdemo2 SET variant1 = PARSE_JSON(varchar1);

Copy

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;

Copy

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

Copy

+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+
| 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                                                   |
+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+

Comparaison de PARSE_JSON avec TO_VARIANT¶

Bien que la fonction PARSE_JSON et la fonction TO_VARIANT puissent prendre une chaîne et renvoyer une valeur VARIANT, elles ne sont pas équivalentes. L’exemple suivant crée une table avec deux colonnes VARIANT. Ensuite, il utilise PARSE_JSON pour insérer une valeur dans une colonne et TO_VARIANT pour insérer une valeur dans l’autre colonne.

CREATE OR REPLACE TABLE jdemo3 (
  variant1 VARIANT,
  variant2 VARIANT);

INSERT INTO jdemo3 (variant1, variant2)
  SELECT
    PARSE_JSON('{"PI":3.14}'),
    TO_VARIANT('{"PI":3.14}');

Copy

La requête ci-dessous montre que les fonctions ont renvoyé des valeurs VARIANT qui stockent des valeurs de différents types de données.

SELECT variant1,
       TYPEOF(variant1),
       variant2,
       TYPEOF(variant2),
       variant1 = variant2
  FROM jdemo3;

Copy

+--------------+------------------+-----------------+------------------+---------------------+
| VARIANT1     | TYPEOF(VARIANT1) | VARIANT2        | TYPEOF(VARIANT2) | VARIANT1 = VARIANT2 |
|--------------+------------------+-----------------+------------------+---------------------|
| {            | OBJECT           | "{\"PI\":3.14}" | VARCHAR          | False               |
|   "PI": 3.14 |                  |                 |                  |                     |
| }            |                  |                 |                  |                     |
+--------------+------------------+-----------------+------------------+---------------------+