Fonctions JSON en langage SQL standard

BigQuery accepte les fonctions ci-après, qui peuvent récupérer et transformer des données JSON.

Présentation de la fonction

Les fonctions suivantes utilisent des guillemets doubles pour échapper les caractères JSONPath non valides : "a.b".

Ce comportement est conforme à la norme ANSI.

Fonction JSON Description Type renvoyé
JSON_QUERY Extrait une valeur JSON, telle qu'un tableau ou un objet, ou une valeur scalaire au format JSON, telle qu'une chaîne, un nombre ou une valeur booléenne. STRING au format JSON
JSON_VALUE Extrait une valeur scalaire. Une valeur scalaire peut représenter une chaîne, un nombre ou une valeur booléenne. Supprime les guillemets les plus externes et l'échappement des valeurs. Renvoie un résultat SQL NULL si une valeur non scalaire est sélectionnée. STRING
JSON_QUERY_ARRAY Extrait un tableau de valeurs JSON, telles que des tableaux ou des objets, et des valeurs scalaires au format JSON, telles que des chaînes, des nombres et des valeurs booléennes. ARRAY<JSON-formatted STRING>
JSON_VALUE_ARRAY Extrait un tableau de valeurs scalaires. Une valeur scalaire peut représenter une chaîne, un nombre ou une valeur booléenne. Supprime les guillemets les plus externes et l'échappement des valeurs. Renvoie un résultat SQL NULL si la valeur sélectionnée n'est pas un tableau, ou s'il ne s'agit pas d'un tableau ne contenant que des valeurs scalaires. ARRAY<STRING>

Anciennes fonctions d'extraction JSON

Les fonctions suivantes utilisent des guillemets simples et des crochets pour échapper les caractères JSONPath non valides : ['a.b'].

Bien que ces fonctions soient compatibles avec BigQuery, nous vous recommandons d'utiliser celles figurant dans le tableau précédent.

Fonction JSON Description Type renvoyé
JSON_EXTRACT Extrait une valeur JSON, telle qu'un tableau ou un objet, ou une valeur scalaire au format JSON, telle qu'une chaîne, un nombre ou une valeur booléenne. STRING au format JSON
JSON_EXTRACT_SCALAR Extrait une valeur scalaire. Une valeur scalaire peut représenter une chaîne, un nombre ou une valeur booléenne. Supprime les guillemets les plus externes et l'échappement des valeurs. Renvoie un résultat SQL NULL si une valeur non scalaire est sélectionnée. STRING
JSON_EXTRACT_ARRAY Extrait un tableau de valeurs JSON, telles que des tableaux ou des objets, et des valeurs scalaires au format JSON, telles que des chaînes, des nombres et des valeurs booléennes. ARRAY<JSON-formatted STRING>
JSON_EXTRACT_STRING_ARRAY Extrait un tableau de valeurs scalaires. Une valeur scalaire peut représenter une chaîne, un nombre ou une valeur booléenne. Supprime les guillemets les plus externes et l'échappement des valeurs. Renvoie un résultat SQL NULL si la valeur sélectionnée n'est pas un tableau, ou s'il ne s'agit pas d'un tableau ne contenant que des valeurs scalaires. ARRAY<STRING>

Autres fonctions JSON

Fonction JSON Description Type renvoyé
TO_JSON_STRING Prend une valeur SQL et renvoie une représentation de la valeur sous forme de chaîne au format JSON. STRING au format JSON

JSON_EXTRACT

JSON_EXTRACT(json_string_expr, json_path)

Description

Extrait une valeur JSON, telle qu'un tableau ou un objet, ou une valeur scalaire au format JSON, telle qu'une chaîne, un nombre ou une valeur booléenne. Si une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets simples et de crochets.

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

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

    Extrait un NULL SQL lorsqu'une chaîne au format JSON "null" est présente. Exemple :

    SELECT JSON_EXTRACT("null", "$") -- Returns a SQL NULL

  • json_path : format JSONPath. Identifie les données que vous souhaitez obtenir à partir de l'entrée. Si ce paramètre facultatif n'est pas renseigné, le symbole JSONPath $ est appliqué, ce qui signifie que toutes les données sont analysées.

    SELECT JSON_EXTRACT('{"a":null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_EXTRACT('{"a":null}', "$.b"); -- Returns a SQL NULL

Si vous souhaitez inclure des valeurs non scalaires telles que des tableaux dans l'extraction, utilisez JSON_EXTRACT. Si vous ne souhaitez extraire que des valeurs scalaires telles que des chaînes, des nombres et des valeurs booléennes, utilisez JSON_EXTRACT_SCALAR.

Type renvoyé

STRING au format JSON

Exemples

Dans les exemples suivants, les données JSON sont extraites et renvoyées sous forme de chaînes au format JSON.

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

JSON_QUERY

JSON_QUERY(json_string_expr, json_path)

Description

Extrait une valeur JSON, telle qu'un tableau ou un objet, ou une valeur scalaire au format JSON, telle qu'une chaîne, un nombre ou une valeur booléenne. Si une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets doubles.

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

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

    Extrait un NULL SQL lorsqu'une chaîne au format JSON "null" est présente. Exemple :

    SELECT JSON_QUERY("null", "$") -- Returns a SQL NULL

  • json_path : format JSONPath. Identifie les données que vous souhaitez obtenir à partir de l'entrée. Si ce paramètre facultatif n'est pas renseigné, le symbole JSONPath $ est appliqué, ce qui signifie que toutes les données sont analysées.

    SELECT JSON_QUERY('{"a":null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_QUERY('{"a":null}', "$.b"); -- Returns a SQL NULL

Si vous souhaitez inclure des valeurs non scalaires telles que des tableaux dans l'extraction, utilisez JSON_QUERY. Si vous ne souhaitez extraire que des valeurs scalaires telles que des chaînes, des nombres et des valeurs booléennes, utilisez JSON_VALUE.

Type renvoyé

STRING au format JSON

Exemples

Dans les exemples suivants, les données JSON sont extraites et renvoyées sous forme de chaînes au format JSON.

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

JSON_EXTRACT_SCALAR

JSON_EXTRACT_SCALAR(json_string_expr[, json_path])

Description

Extrait une valeur scalaire, puis la renvoie sous forme de chaîne. Une valeur scalaire peut représenter une chaîne, un nombre ou une valeur booléenne. Supprime les guillemets les plus externes et l'échappement des valeurs renvoyées. Si une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets simples et de crochets.

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

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

  • json_path : format JSONPath. Identifie les données que vous souhaitez obtenir à partir de l'entrée. Si ce paramètre facultatif n'est pas renseigné, le symbole JSONPath $ est appliqué, ce qui signifie que toutes les données sont analysées.

    Si json_path renvoie un résultat JSON null ou une valeur non scalaire (en d'autres termes, si json_path fait référence à un objet ou à un tableau), un résultat SQL NULL est renvoyé. 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

Si vous ne souhaitez extraire que des valeurs scalaires telles que des chaînes, des nombres et des valeurs booléennes, utilisez JSON_EXTRACT_SCALAR. Si vous souhaitez inclure des valeurs non scalaires telles que des tableaux dans l'extraction, utilisez JSON_EXTRACT.

Type renvoyé

STRING

Exemples

L'exemple suivant compare le mode d'envoi des résultats des fonctions JSON_EXTRACT et JSON_EXTRACT_SCALAR.

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

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

SELECT JSON_EXTRACT('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract,
  JSON_EXTRACT_SCALAR('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract_scalar;

+--------------------+---------------------+
| json_extract       | json_extract_scalar |
+--------------------+---------------------+
| ["apple","banana"] | 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, [' ']. Exemple :

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

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

JSON_VALUE

JSON_VALUE(json_string_expr[, json_path])

Description

Extrait une valeur scalaire, puis la renvoie sous forme de chaîne. Une valeur scalaire peut représenter une chaîne, un nombre ou une valeur booléenne. Supprime les guillemets les plus externes et l'échappement des valeurs renvoyées. Si une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets doubles.

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

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

  • json_path : format JSONPath. Identifie les données que vous souhaitez obtenir à partir de l'entrée. Si ce paramètre facultatif n'est pas renseigné, le symbole JSONPath $ est appliqué, ce qui signifie que toutes les données sont analysées.

    Si json_path renvoie un résultat JSON null ou une valeur non scalaire (en d'autres termes, si json_path fait référence à un objet ou à un tableau), un résultat SQL NULL est renvoyé. 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

Si vous ne souhaitez extraire que des valeurs scalaires telles que des chaînes, des nombres et des valeurs booléennes, utilisez JSON_VALUE. Si vous souhaitez inclure des valeurs non scalaires telles que des tableaux dans l'extraction, utilisez JSON_QUERY.

Type renvoyé

STRING

Exemples

L'exemple suivant compare le mode d'envoi des résultats des fonctions JSON_QUERY et JSON_VALUE.

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

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

SELECT JSON_QUERY('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_query,
  JSON_VALUE('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_value;

+--------------------+------------+
| json_query         | json_value |
+--------------------+------------+
| ["apple","banana"] | NULL       |
+--------------------+------------+

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

JSON_EXTRACT_ARRAY

JSON_EXTRACT_ARRAY(json_string_expr[, json_path])

Description

Extrait un tableau de valeurs JSON, telles que des tableaux ou des objets, et des valeurs scalaires au format JSON, telles que des chaînes, des nombres et des valeurs booléennes. Si une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets simples et de crochets.

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

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

  • json_path : format JSONPath. Identifie les données que vous souhaitez obtenir à partir de l'entrée. Si ce paramètre facultatif n'est pas renseigné, le symbole JSONPath $ est appliqué, ce qui signifie que toutes les données sont analysées.

    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.

Type renvoyé

ARRAY<JSON-formatted 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 :

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

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

-- Strips 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 la propriété 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 :

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

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

-- The queries above produce the following 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.
SELECT JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo"}','$.a') AS result;

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

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

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

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

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

JSON_QUERY_ARRAY

JSON_QUERY_ARRAY(json_string_expr[, json_path])

Description

Extrait un tableau de valeurs JSON, telles que des tableaux ou des objets, et des valeurs scalaires au format JSON, telles que des chaînes, des nombres et des valeurs booléennes. Si une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets doubles.

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

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

  • json_path : format JSONPath. Identifie les données que vous souhaitez obtenir à partir de l'entrée. Si ce paramètre facultatif n'est pas renseigné, le symbole JSONPath $ est appliqué, ce qui signifie que toutes les données sont analysées.

Type renvoyé

ARRAY<JSON-formatted 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_QUERY_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_QUERY_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 :

-- Doesn't strip the double quotes
SELECT JSON_QUERY_ARRAY('["apples","oranges","grapes"]', '$') AS string_array;

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

-- Strips the double quotes
SELECT ARRAY(
  SELECT JSON_VALUE(string_element, '$')
  FROM UNNEST(JSON_QUERY_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 la propriété fruit dans un tableau :

SELECT JSON_QUERY_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 :

SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_array;

-- The queries above produce the following 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 doubles : " ". Exemple :

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

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

Les exemples suivants montrent comment les requêtes non valides et les tableaux vides sont traités :

-- An error is returned if you provide an invalid JSONPath.
SELECT JSON_QUERY_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.a') AS result;

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

-- If a key that does not exist is specified, then the result is NULL.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.b') AS result;

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

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

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

JSON_EXTRACT_STRING_ARRAY

JSON_EXTRACT_STRING_ARRAY(json_string_expr[, json_path])

Description

Extrait un tableau de valeurs scalaires et renvoie un tableau de valeurs scalaires sous forme de chaînes. Une valeur scalaire peut représenter une chaîne, un nombre ou une valeur booléenne. Si une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets simples et de crochets.

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

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

  • json_path : format JSONPath. Identifie les données que vous souhaitez obtenir à partir de l'entrée. Si ce paramètre facultatif n'est pas renseigné, le symbole JSONPath $ est appliqué, ce qui signifie que toutes les données sont analysées.

Type renvoyé

ARRAY<STRING>

Exemples

L'exemple suivant compare le mode d'envoi des résultats des fonctions JSON_EXTRACT_ARRAY et JSON_EXTRACT_STRING_ARRAY.

SELECT JSON_EXTRACT_ARRAY('["apples","oranges"]') AS json_array,
JSON_EXTRACT_STRING_ARRAY('["apples","oranges"]') AS string_array;

+-----------------------+-------------------+
| json_array            | string_array      |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+

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

-- Strips the double quotes
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','$') AS string_array;

+-----------------+
| string_array    |
+-----------------+
| [foo, bar, baz] |
+-----------------+

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_STRING_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

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

Les noms suivants sont équivalents :

SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') AS string_array;

SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

-- The queries above produce the following 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_STRING_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 :

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSON formatted string is invalid, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY('}}','$') AS result;

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

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY(NULL,'$') AS result;

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

-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;

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

-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo"}','$') AS result;

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

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;

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

-- If a JSONPath matches an array of mixed scalar and non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;

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

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

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

-- If a JSONPath matches an array that contains scalar values and a JSON null,
-- then the output of the JSON_EXTRACT_STRING_ARRAY function must be transformed
-- because the final output cannot be an array with NULL values. This example
-- uses the UNNEST operator to convert the output array into a table as the final output.
SELECT string_value FROM UNNEST(JSON_EXTRACT_STRING_ARRAY('["world", 1, null]')) AS string_value;

+--------------+
| string_value |
+--------------+
| world        |
| 1            |
| NULL         |
+--------------+

JSON_VALUE_ARRAY

JSON_VALUE_ARRAY(json_string_expr[, json_path])

Description

Extrait un tableau de valeurs scalaires et renvoie un tableau de valeurs scalaires sous forme de chaînes. Une valeur scalaire peut représenter une chaîne, un nombre ou une valeur booléenne. Si une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets doubles.

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

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

  • json_path : format JSONPath. Identifie les données que vous souhaitez obtenir à partir de l'entrée. Si ce paramètre facultatif n'est pas renseigné, le symbole JSONPath $ est appliqué, ce qui signifie que toutes les données sont analysées.

Type renvoyé

ARRAY<STRING>

Exemples

L'exemple suivant compare le mode d'envoi des résultats des fonctions JSON_QUERY_ARRAY et JSON_VALUE_ARRAY.

SELECT JSON_QUERY_ARRAY('["apples","oranges"]') AS json_array,
       JSON_VALUE_ARRAY('["apples","oranges"]') AS string_array;

+-----------------------+-------------------+
| json_array            | string_array      |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+

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

-- Strips the double quotes
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','$') AS string_array;

+-----------------+
| string_array    |
+-----------------+
| [foo, bar, baz] |
+-----------------+

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_VALUE_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

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

Les noms suivants sont équivalents :

SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;
SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_array;

-- The queries above produce the following 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 doubles : " ". Exemple :

SELECT JSON_VALUE_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 :

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSON-formatted string is invalid, then NULL is returned.
SELECT JSON_VALUE_ARRAY('}}','$') AS result;

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

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_VALUE_ARRAY(NULL,'$') AS result;

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

-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;

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

-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo"}','$') AS result;

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

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;

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

-- If a JSONPath matches an array of mixed scalar and non-scalar objects,
-- then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;

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

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

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

-- If a JSONPath matches an array that contains scalar objects and a JSON null,
-- then the output of the JSON_VALUE_ARRAY function must be transformed
-- because the final output cannot be an array with NULL values. This example
-- uses the UNNEST operator to convert the output array into a table as the final output.
SELECT string_value FROM UNNEST(JSON_VALUE_ARRAY('["world", 1, null]')) AS string_value;

+--------------+
| string_value |
+--------------+
| world        |
| 1            |
| NULL         |
+--------------+

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

Description

Prend une valeur SQL et renvoie une représentation de la valeur sous forme de chaîne au format JSON. La valeur doit être un type de données BigQuery compatible. Pour consulter les types de données BigQuery compatibles avec cette fonction et leurs encodages JSON, cliquez ici.

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 renvoyé

STRING au format JSON

Exemples

Convertissez les lignes d'une table en chaînes 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]} |
+----+-------------+--------------------------------+

Convertissez les lignes d'une table en chaînes au format JSON, faciles à lire.

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

Encodages JSON

Le tableau suivant contient des encodages courants utilisés dans le cas d'une valeur SQL encodée sous forme de valeur JSON avec la fonction TO_JSON_STRING.

Depuis SQL Vers JSON Exemples
NULL

null

 Entrée SQL : NULL
Sortie JSON : null
BOOL boolean Entrée SQL : TRUE
Sortie JSON : true
Entrée SQL : FALSE
Sortie JSON : false
INT64

nombre ou chaîne

Encodé sous forme de nombre lorsque la valeur 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. Une valeur non comprise dans cette plage est encodée sous forme de chaîne.

Entrée SQL : 9007199254740992
Sortie JSON : 9007199254740992
Entrée SQL : 9007199254740993
JSON résultat : "9007199254740993"
NUMERIC
BIGNUMERIC

nombre ou chaîne

Encodé sous forme de nombre lorsque la valeur est comprise dans la plage [-253, 253] et qu'elle n'a pas de partie fractionnaire. Une valeur non comprise dans cette plage est encodée sous forme de chaîne.

Entrée SQL : -1
Sortie JSON : -1
Entrée SQL : 0
Sortie JSON : 0
Entrée SQL : 9007199254740993
Résultat JSON : "9007199254740993"
Entrée SQL : 123.56
Sortie JSON : "123.56"
FLOAT64

nombre ou chaîne

+/-inf et NaN sont encodés au format Infinity, -Infinity et NaN. Dans le cas contraire, cette valeur est encodée sous forme de nombre.

Entrée SQL : 1.0
Sortie JSON : 1
Entrée SQL : 9007199254740993
Sortie JSON : 9007199254740993
Entrée SQL : "+inf"
Sortie JSON : "Infinity"
Entrée SQL : "-inf"
Sortie JSON : "-Infinity"
Entrée SQL : "NaN"
Sortie JSON : "NaN"
CHAÎNE

chaîne

Encodé sous la forme d'une chaîne, é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.

Entrée SQL : "abc"
Résultat JSON : "abc"
Entrée SQL : "\"abc\""
Sortie JSON : "\"abc\""
BYTES

chaîne

Utilise l'encodage de données Base64 RFC 4648.

 Entrée SQL : b"Google"
Sortie JSON : "R29vZ2xl"
DATE chaîne Entrée SQL : DATE '2017-03-06'
Sortie JSON : "2017-03-06"
TIMESTAMP

chaîne

Encodé au format date et heure ISO 8601, où T sépare la date et l'heure et Z (Zulu/UTC) représente le fuseau horaire.

Entrée SQL : TIMESTAMP '2017-03-06 12:34:56.789012'
Sortie JSON : "2017-03-06T12:34:56.789012Z"
DATETIME

chaîne

Encodé au format date et heure ISO 8601, où T sépare la date et l'heure.

Entrée SQL : DATETIME '2017-03-06 12:34:56.789012'
Sortie JSON : "2017-03-06T12:34:56.789012"
TIME

chaîne

Encodé au format heure ISO 8601.

 Entrée SQL : TIME '12:34:56.789012'
Sortie JSON : "12:34:56.789012"
ARRAY

tableau

Peut contenir zéro, un ou plusieurs éléments.

Entrée SQL : ["red", "blue", "green"]
Sortie JSON : ["red", "blue", "green"]
Entrée SQL :[1, 2, 3]
Sortie JSON :[1, 2, 3]
STRUCT

objet

L'objet peut contenir zéro, une ou plusieurs paires clé/valeur. Chaque valeur est formatée en fonction de son type.

Pour TO_JSON_STRING, un champ et tous les doublons de ce champ sont inclus dans la chaîne de sortie.

Les champs anonymes sont représentés par "".

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.

Entrée SQL : STRUCT(12 AS purchases, TRUE AS inStock)
Sortie JSON : {"inStock": true,"purchases":12}

JSONPath

La plupart des fonctions JSON sont transmises dans un paramètre json_string_expr et un paramètre json_path. Le paramètre json_string_expr transmet une chaîne au format JSON, et le paramètre json_path 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 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 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.