Fonctions JSON en langage SQL standard

BigQuery est compatible avec des fonctions permettant de récupérer des données stockées dans des chaînes au format JSON, et avec des fonctions permettant de transformer des données en chaînes au format JSON.

JSON_EXTRACT ou JSON_EXTRACT_SCALAR

JSON_EXTRACT(json_string_expr, json_path_string_literal) renvoie des valeurs JSON sous forme de chaîne (STRING).

JSON_EXTRACT_SCALAR(json_string_expr, json_path_string_literal) renvoie des valeurs JSON scalaires sous forme de chaîne (STRING).

Description

Le paramètre json_string_expr doit être une chaîne au format JSON. Par exemple :

{"class" : {"students" : [{"name" : "Jane"}]}}

Le paramètre json_path_string_literal identifie la ou les valeurs que vous souhaitez obtenir à partir de la chaîne au format JSON. Vous devez construire ce paramètre en utilisant le format JSONPath. Selon les règles applicables à ce format, ce paramètre doit commencer par un symbole $, qui fait référence au niveau le plus externe (le plus haut niveau) de la chaîne au format JSON. Vous pouvez identifier les valeurs enfants à l'aide de la notation par points ou de la notation entre crochets. Si l'objet JSON est un tableau, des crochets peuvent alors vous permettre de spécifier l'index de tableau.

JSONPath	Description
$	Objet ou élément racine
. ou []	Opérateur enfant
[]	Opérateur indice

Les deux fonctions renvoient NULL si le paramètre json_path_string_literal ne correspond pas à une valeur contenue dans json_string_expr. Si la valeur sélectionnée pour JSON_EXTRACT_SCALAR n'est pas scalaire, telle qu'un objet ou un tableau, la fonction renvoie NULL.

Si le format "JSONPath" n'est pas valide, ces fonctions génèrent une erreur.

Dans les cas où une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets simples et de crochets ([' ']). Exemple :

SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}', "$['a.b'].c") as hello;

+-------+
| hello |
+-------+
| world |
+-------+

Exemples

SELECT JSON_EXTRACT(json_text, '$') AS json_text_string
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

La requête ci-dessus produit le résultat suivant :

+-----------------------------------------------------------+
| json_text_string                                          |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}}                  |
| {"class":{"students":[]}}                                 |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+

SELECT JSON_EXTRACT(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

La requête ci-dessus produit le résultat suivant :

+-----------------+
| first_student   |
+-----------------+
| {"name":"Jane"} |
| NULL            |
| {"name":"John"} |
+-----------------+

SELECT JSON_EXTRACT(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

La requête ci-dessus produit le résultat suivant :

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| {"first":"Jamie"} |
+-------------------+

SELECT JSON_EXTRACT(json_text, "$.class['students']") AS student_names
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

La requête ci-dessus produit le résultat suivant :

+------------------------------------+
| student_names                      |
+------------------------------------+
| [{"name":"Jane"}]                  |
| []                                 |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+

SELECT JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
  JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;

La requête ci-dessus produit le résultat suivant :

+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob"   | Jakob       | "6"      | 6      |
+-----------+-------------+----------+--------+

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

Description

Renvoie une représentation de la valeur (value) sous forme de chaîne JSON. Cette fonction accepte un paramètre facultatif, pretty_print. Si le paramètre pretty_print est présent, la valeur renvoyée est formatée pour une meilleure lisibilité.

Type de données d'entrée	Valeur renvoyée
NULL de n'importe quel type	`null`
BOOL	`true` ou `false`
INT64	Identique à `CAST(value AS STRING)` lorsque la valeur (`value`) est comprise dans la plage [-2⁵³, 2⁵³], qui correspond à la plage d'entiers pouvant être représentés sans perte sous forme de nombres à virgule flottante IEEE 754 à deux décimales. Les valeurs situées en dehors de cette plage sont représentées sous forme de chaînes entre guillemets. Exemple : `-1` `0` `12345678901` `9007199254740992` `-9007199254740992` `"9007199254740993"` La valeur `9007199254740993` est supérieure à 2⁵³. Elle est donc représentée par une chaîne entre guillemets.
NUMERIC	Identique à `CAST(value AS STRING)` lorsque la valeur (`value`) est comprise dans la plage [-2⁵³, 2⁵³] et qu'elle n'a pas de partie fractionnaire. Les valeurs situées en dehors de cette plage sont représentées sous forme de chaînes entre guillemets. Exemple : `-1` `0` `"9007199254740993"` `"123.56"`
FLOAT64	`+/-inf` et `NaN` sont représentées respectivement par `Infinity`, `-Infinity` et `NaN`. Sinon, elles sont identiques à `CAST(value AS STRING)`.
STRING	Valeur de chaîne citée, échappée conformément à la norme JSON. Plus précisément, `"`, `\` et les caractères de contrôle de `U+0000` à `U+001F` sont échappés.
BYTES	Valeur RFC 4648 citée, échappée en base64. Exemple : `"R29vZ2xl"` est la représentation en base64 des octets `b"Google"`
DATE	Date citée. Exemple : `"2017-03-06"`
TIMESTAMP	Date-heure ISO 8601 citée, où T sépare la date et l'heure et Zulu/UTC représente le fuseau horaire. Exemple : `"2017-03-06T12:34:56.789012Z"`
DATETIME	Date-heure ISO 8601 citée, où T sépare la date et l'heure. Exemple : `"2017-03-06T12:34:56.789012"`
TIME	Heure ISO 8601 citée. Exemple : `"12:34:56.789012"`
ARRAY	`[elem1,elem2,...]`, où chaque élément (`elem`) est formaté en fonction du type d'élément. Exemple avec formatage : [ elem1, elem2, ... ] Où chaque "elem" est formaté en fonction du type d'élément. Le tableau vide est représenté par `[]`.
STRUCT	`{"field_name1":field_value1,"field_name2":field_value2,...}` Où chaque champ `field_value` est formaté en fonction du type d'élément. Exemple avec formatage : { "field_name1": field_value1, "field_name2": field_value2, ... } Où chaque valeur `field_value` est formatée en fonction du type d'élément. Si une valeur `field_value` est un tableau (ARRAY) ou une structure (STRUCT) non vide, les éléments sont mis en retrait au niveau approprié. La structure vide est représentée par `{}`. Si plusieurs champs portent le même nom, il pourrait être impossible d'analyser la valeur JSON. Les champs anonymes sont représentés par `""`. Si des noms de champs UTF-8 ne sont pas valides, il pourrait être impossible d'analyser la valeur JSON. Les valeurs de chaîne sont échappées conformément à la norme JSON. Plus précisément, `"`, `\` et les caractères de contrôle de `U+0000` à `U+001F` sont échappés.

Type renvoyé

Représentation de la valeur sous forme de chaîne JSON.

Exemples

Convertir les lignes d'une table au format JSON.

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  t,
  TO_JSON_STRING(t) AS json_row
FROM Input AS t;

La requête ci-dessus produit le résultat suivant :

+-----------------------------------+-------------------------------------------------------+
| t                                 | json_row                                              |
+-----------------------------------+-------------------------------------------------------+
| {[1, 2], foo, {true, 2017-04-05}} | {"x":[1,2],"y":"foo","s":{"a":true,"b":"2017-04-05"}} |
| {NULL, , {false, 0001-01-01}}     | {"x":null,"y":"","s":{"a":false,"b":"0001-01-01"}}    |
| {[3], bar, {NULL, 2016-12-05}}    | {"x":[3],"y":"bar","s":{"a":null,"b":"2016-12-05"}}   |
+-----------------------------------+-------------------------------------------------------+

Convertir les lignes d'une table au format JSON avec formatage.

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  TO_JSON_STRING(t, true) AS json_row
FROM Input AS t;

La requête ci-dessus produit le résultat suivant :

+-----------------------+
| json_row              |
+-----------------------+
| {                     |
|  "x": [               |
|    1,                 |
|    2                  |
|  ],                   |
|  "y": "foo",          |
|  "s": {               |
|    "a": true,         |
|    "b": "2017-04-05"  |
|  }                    |
|}                      |
| {                     |
|  "x": null,           |
|  "y": "",             |
|  "s": {               |
|    "a": false,        |
|    "b": "0001-01-01"  |
|  }                    |
|}                      |
| {                     |
|  "x": [               |
|    3                  |
|  ],                   |
|  "y": "bar",          |
|  "s": {               |
|    "a": null,         |
|    "b": "2016-12-05"  |
|  }                    |
|}                      |
+-----------------------+

Cette page vous a-t-elle été utile ? Évaluez-la :

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Dernière mise à jour : juillet 16, 2019

Fonctions JSON en langage SQL standard

JSON_EXTRACT ou JSON_EXTRACT_SCALAR

TO_JSON_STRING

Envoyer des commentaires concernant…