Fonctions JSON en langage SQL standard

BigQuery accepte des fonctions permettant de récupérer des données stockées dans des chaînes au format JSON, ainsi que 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_format), qui renvoie des valeurs JSON sous forme de chaîne (STRING).

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

Description

Extrait les valeurs JSON ou les valeurs scalaires JSON en tant que chaînes.

  • json_string_expr : chaîne au format JSON. Exemple :

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path_format : format JSONpath. Identifie la ou les valeurs que vous souhaitez obtenir à partir de la chaîne au format JSON. Si json_path_format renvoie un résultat JSON null, celui-ci est converti en résultat SQL NULL.

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.

Type renvoyé

STRING

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;

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

+-----------------+
| 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" : null}]}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| NULL              |
| "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;

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

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

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

JSON_EXTRACT_ARRAY

JSON_EXTRACT_ARRAY(json_string_expr[, json_path_format])

Description

Extrait un tableau d'une chaîne au format JSON.

  • json_string_expr : chaîne au format JSON. Exemple :

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path_format : format JSONpath. Identifie la ou les valeurs que vous souhaitez obtenir à partir de la chaîne au format JSON. Si ce paramètre facultatif n'est pas renseigné, le symbole JSONPath $ est appliqué, ce qui signifie que l'intégralité de la chaîne au format JSON est analysée.

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.

Type renvoyé

ARRAY<STRING>

Exemples

L'opération suivante extrait les éléments d'une chaîne au format JSON dans un tableau de chaînes :

SELECT JSON_EXTRACT_ARRAY('[1,2,3]') as string_array

+----------------+
| string_array   |
+----------------+
| ['1','2','3']  |
+----------------+

L'opération suivante extrait un tableau de chaînes et le convertit en tableau entier :

SELECT ARRAY(
  SELECT CAST(integer_element as INT64)
  FROM UNNEST(
    JSON_EXTRACT_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array

+---------------+
| integer_array |
+---------------+
| [1,2,3]       |
+---------------+

L'opération suivante extrait les valeurs de chaîne d'une chaîne au format JSON dans un tableau :

-- Don't strip the double quotes
SELECT JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]', '$') as string_array

+--------------------------------------+
| string_array                         |
+--------------------------------------+
| ['"apples"','"oranges"','"grapes"']  |
+--------------------------------------+

-- Strip the double quotes
SELECT ARRAY(
  SELECT JSON_EXTRACT_SCALAR(string_element, '$')
  FROM UNNEST(JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array

+---------------------------------+
|  string_array                   |
+---------------------------------+
| ['apples', 'oranges', 'grapes'] |
+---------------------------------+

L'opération suivante extrait uniquement les éléments de fruit dans un tableau :

SELECT JSON_EXTRACT_ARRAY(
  '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}}',
  '$.fruit'
) as string_array

+----------------------------------------------------------------------+
| string_array                                                         |
+----------------------------------------------------------------------+
| ['{"apples" : 5, "oranges" : 10}' , '{"apples" : 2, "oranges" : 4}'] |
+----------------------------------------------------------------------+

Les noms suivants sont équivalents :

JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') as string_array

JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') as string_array

-- The queries above produce this result:
+---------------------------------------+
|  string_array                         |
+---------------------------------------+
| [""apples"", ""oranges"", ""grapes""] |
+---------------------------------------+

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_ARRAY('{"a.b": {"c": "world"}}', "$['a.b'].c") as hello;

+---------+
| hello   |
+---------+
| [world] |
+---------+

Les exemples suivants examinent la manière dont les requêtes non valides et les tableaux vides sont traités :

  • Si un chemin JSONPath n'est pas valide, une erreur est renvoyée.
  • Si une chaîne au format JSON n'est pas valide, le résultat est NULL.
  • Vous pouvez utiliser des tableaux vides dans la chaîne au format JSON.
-- An error is thrown if you provide an invalid JSONPath.
JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') as result

-- If a key is not specified when a key is expected, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$') as result

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a key that does not exist is specified, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$.b') as result

+--------+
| result |
+--------+
| NULL   |
+--------+

-- Empty arrays in JSON-formatted strings are supported.
JSON_EXTRACT_ARRAY('{"a":"foo","b":[]}','$.b') as result

+--------+
| result |
+--------+
| []     |
+--------+

JSON_QUERY ou JSON_VALUE

JSON_QUERY(json_string_expr, json_path_format), qui renvoie des valeurs JSON sous forme de chaîne (STRING).

JSON_VALUE(json_string_expr, json_path_format), qui renvoie des valeurs JSON scalaires sous forme de chaîne (STRING).

Description

Extrait les valeurs JSON ou les valeurs scalaires JSON en tant que chaînes.

  • json_string_expr : chaîne au format JSON. Exemple :

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path_format : format JSONpath. Identifie la ou les valeurs que vous souhaitez obtenir à partir de la chaîne au format JSON. Si json_path_format renvoie un résultat JSON null, celui-ci est converti en résultat SQL NULL.

Dans les cas où une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets doubles.

Exemples

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

+-----------------------------------------------------------+
| json_text_string                                          |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}}                  |
| {"class":{"students":[]}}                                 |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_QUERY(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;

+-----------------+
| first_student   |
+-----------------+
| {"name":"Jane"} |
| NULL            |
| {"name":"John"} |
+-----------------+
SELECT JSON_QUERY(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| NULL              |
| "Jamie"           |
+-------------------+
SELECT JSON_QUERY(json_text, '$.class."students"') AS student_names
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+------------------------------------+
| student_names                      |
+------------------------------------+
| [{"name":"Jane"}]                  |
| []                                 |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
  JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;

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

Dans les cas où une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets doubles. Exemple :

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

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

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

Description

Renvoie une représentation de value sous forme de chaîne JSON. Cette fonction accepte un paramètre booléen facultatif appelé pretty_print. Si pretty_print est true, la valeur renvoyée est formatée afin d'offrir 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 [-253, 253], qui correspond à la plage d'entiers pouvant être représentés sans perte sous forme de nombres à virgule flottante avec deux décimales, définis par la norme IEEE 754. Les valeurs non comprises dans 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 à 253. Elle est donc représentée sous forme de chaîne entre guillemets.

NUMERIC

Identique à CAST(value AS STRING) lorsque la valeur value est comprise dans la plage [-253, 253] et qu'elle n'a pas de partie fractionnaire. Les valeurs non comprises dans cette plage sont représentées sous forme de chaînes entre guillemets. Par exemple :

-1
0
"9007199254740993"
"123.56"
FLOAT64 Les valeurs +/-inf et NaN sont représentées respectivement par Infinity, -Infinity et NaN.

Sinon, ce type est identique à CAST(value AS STRING).

STRING Valeur de chaîne entre guillemets, é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 entre guillemets, échappée en base64. Exemple :

"R29vZ2xl" est la représentation en base64 des octets b"Google".

DATE

Date entre guillemets. Exemple :

"2017-03-06"
TIMESTAMP

Date et heure ISO 8601 entre guillemets, où T sépare la date et l'heure, et où Zulu/UTC représente le fuseau horaire. Exemple :

"2017-03-06T12:34:56.789012Z"
DATETIME

Date et heure conforme à l'ISO 8601 et entre guillemets, où "T" sépare la date et l'heure. Exemple :

"2017-03-06T12:34:56.789012"
TIME

Heure ISO 8601 entre guillemets. Exemple :

"12:34:56.789012"
ARRAY

Tableau de zéro ou plusieurs éléments. Chaque élément est formaté en fonction de son type.

Exemple sans mise en forme :


["red", "blue", "green"]

Exemple avec mise en forme :


[
  "red",
  "blue",
  "green"
]
STRUCT

Objet contenant zéro ou plusieurs paires clé/valeur. Chaque valeur est formatée en fonction de son type.

Exemple sans mise en forme :


{"colors":["red","blue"],"purchases":12,"inStock": true}

Exemple avec mise en forme :


{
  "color":[
    "red",
    "blue"
   ]
  "purchases":12,
  "inStock": true
}

Si les champs ont des noms en double, il est possible que la chaîne JSON ne puisse pas être analysée. Les champs anonymes sont représentés par "". Si un champ est un tableau ou un objet non vide, les éléments/champs sont mis en retrait au niveau approprié.

Si des champs UTF-8 ont des noms non valides, il est possible que la chaîne JSON ne puisse pas être analysée. 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

L'exemple suivant convertit les lignes d'une table au format JSON.

With CoordinatesTable AS (
    (SELECT 1 AS id, [10,20] AS coordinates) UNION ALL
    (SELECT 2 AS id, [30,40] AS coordinates) UNION ALL
    (SELECT 3 AS id, [50,60] AS coordinates))
SELECT id, coordinates, TO_JSON_STRING(t) AS json_data
FROM CoordinatesTable as t;

+--------+-------------+--------------------------------+
| id     | coordinates | json_data                      |
+--------+-------------+--------------------------------+
| 1      | [10,20]     | {"id":1,"coordinates":[10,20]} |
| 2      | [30,40]     | {"id":2,"coordinates":[30,40]} |
| 3      | [50,60]     | {"id":3,"coordinates":[50,60]} |
+--------+-------------+--------------------------------+

L'exemple suivant convertit les lignes d'une table au format JSON et applique une mise en forme.

With CoordinatesTable AS (
    (SELECT 1 AS id, [10,20] AS coordinates) UNION ALL
    (SELECT 2 AS id, [30,40] AS coordinates)
SELECT id, coordinates, TO_JSON_STRING(t, true) AS json_data
FROM CoordinatesTable as t;

+--------+-------------+---------------------+
| id     | coordinates | json_data           |
+--------+-------------+---------------------+
| 1      | [10,20]     | {                   |
|        |             |   "id":1,           |
|        |             |   "coordinates":[   |
|        |             |     10,             |
|        |             |     20              |
|        |             |   ]                 |
|        |             | }                   |
| 2      | [30,40]     | {                   |
|        |             |   "id":2,           |
|        |             |   "coordinates":[   |
|        |             |     30,             |
|        |             |     40              |
|        |             |   ]                 |
|        |             | }                   |
+--------+-------------+---------------------+

Format JSONPath

La plupart des fonctions JSON sont transmises dans un paramètre json_string_expr et un paramètre json_path_format. Le paramètre json_string_expr transmet une chaîne au format JSON, et le paramètre json_path_format identifie la ou les valeurs que vous souhaitez obtenir de la chaîne au format JSON.

Le paramètre json_string_expr doit être une chaîne JSON dont le format est le suivant :

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

Vous construisez le paramètre json_path_format 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 points. Si l'objet JSON est un tableau, vous pouvez utiliser des crochets pour spécifier l'index de tableau. Si les clés contiennent $, des points ou des crochets, reportez-vous à chaque fonction JSON pour savoir comment les échapper.

JSONPath Description Exemple Résultat utilisant le code json_string_expr ci-dessus
$ Objet ou élément racine "$" {"class":{"students":[{"name":"Jane"}]}}
. Opérateur enfant "$.class.students" [{"name":"Jane"}]
[] Opérateur indice "$.class.students[0]" {"name":"Jane"}

Une fonction JSON renvoie NULL si le paramètre json_path_format ne correspond pas à une valeur dans json_string_expr. Si la valeur sélectionnée pour une fonction scalaire n'est pas scalaire, telle qu'un objet ou un tableau, la fonction renvoie NULL.

Si le format JSONPath n'est pas valide, la fonction génère une erreur.