Fonctions JSON

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

Présentation de la fonction

Fonctions d'extraction JSON standards (recommandé)

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 ou JSON 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> ou ARRAY<JSON>
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 ou JSON 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> ou ARRAY<JSON>
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é
PARSE_JSON	Utilise une chaîne au format JSON et renvoie une valeur JSON.	JSON
TO_JSON	Prend une valeur SQL et renvoie une valeur JSON.	JSON
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
STRING	Extrait une chaîne du format JSON.	STRING
BOOL	Extrait une valeur booléenne de JSON.	BOOL
INT64	Extrait un entier de 64 bits de JSON.	INT64
FLOAT64	Extrait un nombre à virgule flottante de 64 bits de JSON.	FLOAT64
JSON_TYPE	Renvoie le type de la valeur JSON la plus externe en tant que chaîne.	STRING

JSON_EXTRACT

JSON_EXTRACT(json_string_expr, json_path)

JSON_EXTRACT(json_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 une valeur NULL SQL lorsqu'une chaîne au format JSON null est présente. Exemple :

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

• json_expr : JSON Exemple :

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

  Extrait un null JSON lorsqu'un null JSON est rencontré.

  SELECT JSON_EXTRACT(JSON 'null', "$") -- Returns a JSON 'null'
  

• json_path : format JSONPath. Identifie les données que vous souhaitez obtenir à partir de l'entrée.

Type renvoyé

• json_string_expr : STRING au format JSON
• json_expr : JSON

Exemples

Dans l'exemple suivant, les données JSON sont extraites et renvoyées au format JSON.

SELECT
  JSON_EXTRACT(JSON '{"class":{"students":[{"id":5},{"id":12}]}}', '$.class')
  AS json_data;

+-----------------------------------+
| json_data                         |
+-----------------------------------+
| {"students":[{"id":5},{"id":12}]} |
+-----------------------------------+

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

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

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

JSON_QUERY

JSON_QUERY(json_string_expr, json_path)

JSON_QUERY(json_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 une valeur NULL SQL lorsqu'une chaîne au format JSON null est présente. Exemple :

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

• json_expr : JSON Exemple :

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

  Extrait un null JSON lorsqu'un null JSON est rencontré.

  SELECT JSON_QUERY(JSON 'null', "$") -- Returns a JSON 'null'
  

• json_path : format JSONPath. Identifie les données que vous souhaitez obtenir à partir de l'entrée.

Type renvoyé

• json_string_expr : STRING au format JSON
• json_expr : JSON

Exemples

Dans l'exemple suivant, les données JSON sont extraites et renvoyées au format JSON.

SELECT
  JSON_QUERY(JSON '{"class":{"students":[{"id":5},{"id":12}]}}', '$.class')
  AS json_data;

+-----------------------------------+
| json_data                         |
+-----------------------------------+
| {"students":[{"id":5},{"id":12}]} |
+-----------------------------------+

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

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

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

JSON_EXTRACT_SCALAR

JSON_EXTRACT_SCALAR(json_string_expr[, json_path])

JSON_EXTRACT_SCALAR(json_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_expr : JSON Exemple :

  JSON '{"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é.

Type renvoyé

STRING

Exemples

Dans l'exemple suivant, age est extrait.

SELECT JSON_EXTRACT_SCALAR(JSON '{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+------------+
| scalar_age |
+------------+
| 6          |
+------------+

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])

JSON_VALUE(json_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_expr : JSON Exemple :

  JSON '{"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é.

Type renvoyé

STRING

Exemples

Dans l'exemple suivant, les données JSON sont extraites et renvoyées sous forme de valeur scalaire.

SELECT JSON_VALUE(JSON '{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+------------+
| scalar_age |
+------------+
| 6          |
+------------+

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])

JSON_EXTRACT_ARRAY(json_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_expr : JSON Exemple :

  JSON '{"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é

• json_string_expr : ARRAY<JSON-formatted STRING>
• json_expr : ARRAY<JSON>

Exemples

L'opération suivante extrait les éléments au format JSON dans un tableau de valeurs JSON :

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

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

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])

JSON_QUERY_ARRAY(json_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_expr : JSON Exemple :

  JSON '{"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é

• json_string_expr : ARRAY<JSON-formatted STRING>
• json_expr : ARRAY<JSON>

Exemples

L'opération suivante extrait les éléments au format JSON dans un tableau de valeurs JSON :

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

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

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])

JSON_EXTRACT_STRING_ARRAY(json_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_expr : JSON Exemple :

  JSON '{"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.

Mises en garde :

• Une valeur JSON null dans le tableau d'entrée génère une valeur SQL NULL en tant que sortie pour cette valeur JSON null. Si la sortie contient un élément de tableau NULL, une erreur est générée, car le résultat final ne peut pas être un tableau avec des valeurs NULL.
• Si un chemin JSONPath correspond à un tableau contenant des objets scalaires et une valeur JSON null, la sortie de la fonction doit être transformée, car la sortie finale ne peut pas être un tableau avec des valeurs NULL.

Type renvoyé

ARRAY<STRING>

Exemples

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

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

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

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

-- The following query produces and error because the final output cannot be an
-- array with NULLs.
SELECT JSON_EXTRACT_STRING_ARRAY('["world", 1, null]') AS result;

JSON_VALUE_ARRAY

JSON_VALUE_ARRAY(json_string_expr[, json_path])

JSON_VALUE_ARRAY(json_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_expr : JSON Exemple :

  JSON '{"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.

Mises en garde :

• Une valeur JSON null dans le tableau d'entrée génère une valeur SQL NULL en sortie pour la valeur JSON null. Si la sortie contient un élément de tableau NULL, une erreur est générée, car le résultat final ne peut pas être un tableau avec des valeurs NULL.
• Si un chemin JSONPath correspond à un tableau contenant des objets scalaires et une valeur JSON null, la sortie de la fonction doit être transformée, car la sortie finale ne peut pas être un tableau avec des valeurs NULL.

Type renvoyé

ARRAY<STRING>

Exemples

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

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

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

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

-- The following query produces and error because the final output cannot be an
-- array with NULLs.
SELECT JSON_VALUE_ARRAY('["world", 1, null]') AS result;

PARSE_JSON

Bêta

Ce produit ou cette fonctionnalité est couvert par les Conditions des offres pré-DG des conditions d'utilisation de Google Cloud Platform. Les fonctionnalités et produits pré-DG sont susceptibles de présenter une compatibilité limitée, et les modifications apportées à ces fonctionnalités ou produits peuvent ne pas être compatibles avec d'autres versions pré-DG. Pour en savoir plus, consultez les descriptions des étapes de lancement.

Important : Pour vous inscrire à cette version bêta, remplissez le formulaire d'inscription.

PARSE_JSON(json_string_expr[, wide_number_mode=>{ 'exact' | 'round' } ])

Description

Prend une valeur STRING SQL et renvoie une valeur SQL JSON. La valeur STRING représente une valeur JSON au format de chaîne.

Cette fonction accepte un argument facultatif qui doit se nommer wide_number_mode, qui détermine comment gérer les nombres qui ne peuvent pas être stockés dans une valeur JSON sans perte de précision. Si utilisé, wide_number_mode doit inclure l'une des valeurs suivantes :

• exact : n'accepte que les nombres pouvant être stockés sans perte de précision. Si un nombre ne peut pas être stocké sans perte de précision, la fonction génère une erreur.
• round : si un nombre ne peut pas être stocké sans perte de précision, essayez de l'arrondir à un nombre pouvant être stocké sans perte de précision. Si le nombre ne peut pas être arrondi, la fonction génère une erreur.

Si wide_number_mode n'est pas utilisé, la fonction inclut implicitement wide_number_mode=>'exact'. Si un nombre apparaît dans un objet ou un tableau JSON, l'argument wide_number_mode est appliqué au nombre de l'objet ou du tableau.

Les numéros des domaines suivants peuvent être stockés au format JSON sans perte de précision :

• Entiers signés/non signés de 64 bits, tels que INT64
• FLOAT64

Type renvoyé

JSON

Exemples

Dans l'exemple suivant, une chaîne au format JSON est convertie en JSON.

SELECT PARSE_JSON('{"coordinates":[10,20],"id":1}') AS json_data;

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

Les requêtes suivantes échouent pour les raisons suivantes :

• Le nombre transmis ne peut pas être stocké sans perte de précision.
• wide_number_mode=>'exact' est utilisé implicitement dans la première requête et explicitement dans la deuxième requête.

SELECT PARSE_JSON('{"id":922337203685477580701}') AS json_data; -- fails
SELECT PARSE_JSON('{"id":922337203685477580701}', wide_number_mode=>'exact') AS json_data; -- fails

La requête suivante arrondit le nombre à un nombre pouvant être stocké au format JSON.

SELECT PARSE_JSON('{"id":922337203685477580701}', wide_number_mode=>'round') AS json_data;

+--------------------------------+
| json_data                      |
+--------------------------------+
| {"id":9.223372036854776e+20}   |
+--------------------------------+

TO_JSON

Bêta

Ce produit ou cette fonctionnalité est couvert par les Conditions des offres pré-DG des conditions d'utilisation de Google Cloud Platform. Les fonctionnalités et produits pré-DG sont susceptibles de présenter une compatibilité limitée, et les modifications apportées à ces fonctionnalités ou produits peuvent ne pas être compatibles avec d'autres versions pré-DG. Pour en savoir plus, consultez les descriptions des étapes de lancement.

Important : Pour vous inscrire à cette version bêta, remplissez le formulaire d'inscription.

TO_JSON(sql_value[, stringify_wide_numbers=>{ TRUE | FALSE } ])

Description

Prend une valeur SQL et renvoie une valeur 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 argument facultatif qui doit se nommer stringify_wide_numbers.

• Si cet argument est TRUE, les valeurs numériques situées en dehors du domaine de type FLOAT64 sont encodées sous forme de chaînes.
• Si cet argument n'est pas utilisé ou correspond à FALSE, les valeurs numériques situées en dehors du type de domaine FLOAT64 ne sont pas encodées sous forme de chaînes, mais sont stockées sous forme de nombres JSON. Si une valeur numérique ne peut pas être stockée au format JSON sans perte de précision, une erreur est générée.

Les types de données numériques suivants sont affectés par l'argument stringify_wide_numbers :

• INT64
• NUMERIC
• BIGNUMERIC

Si l'un de ces types de données numériques apparaît dans un type de données conteneur comme ARRAY ou STRUCT, l'argument stringify_wide_numbers est appliqué aux types de données numériques du type de données du conteneur.

Type renvoyé

Une valeur JSON

Exemples

Dans l'exemple suivant, la requête convertit les lignes d'une table en valeurs 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 TO_JSON(t) AS json_objects
FROM CoordinatesTable AS t;

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

Dans l'exemple suivant, la requête renvoie une valeur numérique volumineuse sous forme de chaîne JSON.

SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>TRUE) as stringify_on

+--------------------+
| stringify_on       |
+--------------------+
| "9007199254740993" |
+--------------------+

Dans l'exemple suivant, les deux requêtes renvoient une valeur numérique élevée sous forme de nombre JSON.

SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>FALSE) as stringify_off
SELECT TO_JSON(9007199254740993) as stringify_off

+------------------+
| stringify_off    |
+------------------+
| 9007199254740993 |
+------------------+

Dans l'exemple suivant, seules les valeurs numériques volumineuses sont converties en chaînes JSON.

With T1 AS (
  (SELECT 9007199254740993 AS id) UNION ALL
  (SELECT 2 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;

+---------------------------+
| json_objects              |
+---------------------------+
| {"id":"9007199254740993"} |
| {"id":2}                  |
+---------------------------+

Dans cet exemple, les valeurs 9007199254740993 (INT64) et 2.1 (FLOAT64) sont converties vers le supertype commun FLOAT64, qui n'est pas affecté par l'argument stringify_wide_numbers.

With T1 AS (
  (SELECT 9007199254740993 AS id) UNION ALL
  (SELECT 2.1 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;

+------------------------------+
| json_objects                 |
+------------------------------+
| {"id":9.007199254740992e+15} |
| {"id":2.1}                   |
+------------------------------+

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

STRING

Bêta

Ce produit ou cette fonctionnalité est couvert par les Conditions des offres pré-DG des conditions d'utilisation de Google Cloud Platform. Les fonctionnalités et produits pré-DG sont susceptibles de présenter une compatibilité limitée, et les modifications apportées à ces fonctionnalités ou produits peuvent ne pas être compatibles avec d'autres versions pré-DG. Pour en savoir plus, consultez les descriptions des étapes de lancement.

Important : Pour vous inscrire à cette version bêta, remplissez le formulaire d'inscription.

STRING(json_expr)

Description

Prend une expression JSON, extrait une chaîne JSON et renvoie cette valeur sous la forme d'un élément STRING SQL. Si l'expression est NULL SQL, la fonction renvoie NULL SQL. Si la valeur JSON extraite n'est pas une chaîne, une erreur est générée.

• json_expr : JSON Exemple :

  JSON '{"name": "sky", "color" : "blue"}'
  

Type renvoyé

STRING

Exemples

SELECT STRING(JSON '"purple"') AS color;

+--------+
| color  |
+--------+
| purple |
+--------+

SELECT STRING(JSON_QUERY(JSON '{"name": "sky", "color": "blue"}', "$.color")) AS color;

+-------+
| color |
+-------+
| blue  |
+-------+

Les exemples suivants montrent comment sont traitées les requêtes non valides :

-- An error is thrown if the JSON is not of type string.
SELECT STRING(JSON '123') AS result; -- Throws an error
SELECT STRING(JSON 'null') AS result; -- Throws an error
SELECT SAFE.STRING(JSON '123') AS result; -- Returns a SQL NULL

BOOL

Bêta

Ce produit ou cette fonctionnalité est couvert par les Conditions des offres pré-DG des conditions d'utilisation de Google Cloud Platform. Les fonctionnalités et produits pré-DG sont susceptibles de présenter une compatibilité limitée, et les modifications apportées à ces fonctionnalités ou produits peuvent ne pas être compatibles avec d'autres versions pré-DG. Pour en savoir plus, consultez les descriptions des étapes de lancement.

Important : Pour vous inscrire à cette version bêta, remplissez le formulaire d'inscription.

BOOL(json_expr)

Description

Prend une expression JSON, extrait une valeur booléenne JSON et renvoie cette valeur sous la forme d'un élément BOOL SQL. Si l'expression est NULL SQL, la fonction renvoie NULL SQL. Si la valeur JSON extraite n'est pas booléenne, une erreur est générée.

• json_expr : JSON Exemple :

  JSON '{"name": "sky", "color" : "blue"}'
  

Type renvoyé

BOOL

Exemples

SELECT BOOL(JSON 'true') AS vacancy;

+---------+
| vacancy |
+---------+
| true    |
+---------+

SELECT BOOL(JSON_QUERY(JSON '{"hotel class": "5-star", "vacancy": true}', "$.vacancy")) AS vacancy;

+---------+
| vacancy |
+---------+
| true    |
+---------+

Les exemples suivants montrent comment sont traitées les requêtes non valides :

-- An error is thrown if JSON is not of type bool.
SELECT BOOL(JSON '123') AS result; -- Throws an error
SELECT BOOL(JSON 'null') AS result; -- Throw an error
SELECT SAFE.BOOL(JSON '123') AS result; -- Returns a SQL NULL

INT64

Bêta

Ce produit ou cette fonctionnalité est couvert par les Conditions des offres pré-DG des conditions d'utilisation de Google Cloud Platform. Les fonctionnalités et produits pré-DG sont susceptibles de présenter une compatibilité limitée, et les modifications apportées à ces fonctionnalités ou produits peuvent ne pas être compatibles avec d'autres versions pré-DG. Pour en savoir plus, consultez les descriptions des étapes de lancement.

Important : Pour vous inscrire à cette version bêta, remplissez le formulaire d'inscription.

INT64(json_expr)

Description

Prend une expression JSON, extrait un nombre JSON et renvoie cette valeur sous la forme d'un élément INT64 SQL. Si l'expression est NULL SQL, la fonction renvoie NULL SQL. Si le nombre JSON extrait comporte une partie fractionnelle ou se situe en dehors du domaine INT64, une erreur est générée.

• json_expr : JSON Exemple :

  JSON '{"name": "sky", "color" : "blue"}'
  

Type renvoyé

INT64

Exemples

SELECT INT64(JSON '2005') AS flight_number;

+---------------+
| flight_number |
+---------------+
| 2005          |
+---------------+

SELECT INT64(JSON_QUERY(JSON '{"gate": "A4", "flight_number": 2005}', "$.flight_number")) AS flight_number;

+---------------+
| flight_number |
+---------------+
| 2005          |
+---------------+

SELECT INT64(JSON '10.0') AS score;

+-------+
| score |
+-------+
| 10    |
+-------+

Les exemples suivants montrent comment sont traitées les requêtes non valides :

-- An error is thrown if JSON is not a number or cannot be converted to a 64-bit integer.
SELECT INT64(JSON '10.1') AS result;  -- Throws an error
SELECT INT64(JSON '"strawberry"') AS result; -- Throws an error
SELECT INT64(JSON 'null') AS result; -- Throws an error
SELECT SAFE.INT64(JSON '"strawberry"') AS result;  -- Returns a SQL NULL

FLOAT64

Bêta

Ce produit ou cette fonctionnalité est couvert par les Conditions des offres pré-DG des conditions d'utilisation de Google Cloud Platform. Les fonctionnalités et produits pré-DG sont susceptibles de présenter une compatibilité limitée, et les modifications apportées à ces fonctionnalités ou produits peuvent ne pas être compatibles avec d'autres versions pré-DG. Pour en savoir plus, consultez les descriptions des étapes de lancement.

Important : Pour vous inscrire à cette version bêta, remplissez le formulaire d'inscription.

FLOAT64(json_expr[, wide_number_mode=>{ 'exact' | 'round'])

Description

Prend une expression JSON, extrait un nombre JSON et renvoie cette valeur sous la forme d'un élément FLOAT64 SQL. Si l'expression est NULL SQL, la fonction renvoie NULL SQL. Si la valeur JSON extraite n'est pas un nombre, une erreur est générée.

• json_expr : JSON Exemple :

  JSON '{"name": "sky", "color" : "blue"}'
  

Cette fonction accepte un argument facultatif nommé wide_number_mode, qui définit ce qui se passe avec un nombre ne pouvant pas être représenté sous la forme d'une valeur FLOAT64 sans perte de précision.

Cet argument accepte l'une des deux valeurs sensibles à la casse :

• "exact" : la fonction échoue si le résultat ne peut pas être représenté sous forme de FLOAT64 sans perte de précision.
• "round" (arrondi) : la valeur numérique stockée dans JSON sera arrondie à FLOAT64. Si un tel arrondi n'est pas possible, la fonction échoue. Il s'agit de la valeur par défaut si l'argument n'est pas spécifié.

Type renvoyé

FLOAT64

Exemples

SELECT FLOAT64(JSON '9.8') AS velocity;

+----------+
| velocity |
+----------+
| 9.8      |
+----------+

SELECT FLOAT64(JSON_QUERY(JSON '{"vo2_max": 39.1, "age": 18}', "$.vo2_max")) AS vo2_max;

+---------+
| vo2_max |
+---------+
| 39.1    |
+---------+

SELECT FLOAT64(JSON '18446744073709551615', wide_number_mode=>'round') as result;

+------------------------+
| result                 |
+------------------------+
| 1.8446744073709552e+19 |
+------------------------+

SELECT FLOAT64(JSON '18446744073709551615') as result;

+------------------------+
| result                 |
+------------------------+
| 1.8446744073709552e+19 |
+------------------------+

Les exemples suivants montrent comment sont traitées les requêtes non valides :

-- An error is thrown if JSON is not of type FLOAT64.
SELECT FLOAT64(JSON '"strawberry"') AS result;
SELECT FLOAT64(JSON 'null') AS result;

-- An error is thrown because `wide_number_mode` is case-sensitive and not "exact" or "round".
SELECT FLOAT64(JSON '123.4', wide_number_mode=>'EXACT') as result;
SELECT FLOAT64(JSON '123.4', wide_number_mode=>'exac') as result;

-- An error is thrown because the number cannot be converted to DOUBLE without loss of precision
SELECT FLOAT64(JSON '18446744073709551615', wide_number_mode=>'exact') as result;

-- Returns a SQL NULL
SELECT SAFE.FLOAT64(JSON '"strawberry"') AS result;

JSON_TYPE

Bêta

Ce produit ou cette fonctionnalité est couvert par les Conditions des offres pré-DG des conditions d'utilisation de Google Cloud Platform. Les fonctionnalités et produits pré-DG sont susceptibles de présenter une compatibilité limitée, et les modifications apportées à ces fonctionnalités ou produits peuvent ne pas être compatibles avec d'autres versions pré-DG. Pour en savoir plus, consultez les descriptions des étapes de lancement.

Important : Pour vous inscrire à cette version bêta, remplissez le formulaire d'inscription.

JSON_TYPE(json_expr)

Description

Utilise une expression JSON et renvoie le type de la valeur JSON la plus externe en tant que STRING SQL. Les noms de ces types JSON peuvent être renvoyés :

• object
• array
• string
• number
• boolean
• null

Si l'expression est NULL SQL, la fonction renvoie NULL SQL. Si la valeur JSON extraite n'est pas un type JSON valide, une erreur est générée.

• json_expr : JSON Exemple :

  JSON '{"name": "sky", "color" : "blue"}'
  

Type renvoyé

STRING

Exemples

SELECT json_val, JSON_TYPE(json_val) AS type
FROM
  UNNEST(
    [
      JSON '"apple"',
      JSON '10',
      JSON '3.14',
      JSON 'null',
      JSON '{"city": "New York", "State": "NY"}',
      JSON '["apple", "banana"]',
      JSON 'false'
    ]
  ) AS json_val;

+----------------------------------+---------+
| json_val                         | type    |
+----------------------------------+---------+
| "apple"                          | string  |
| 10                               | number  |
| 3.14                             | number  |
| null                             | null    |
| {"State":"NY","city":"New York"} | object  |
| ["apple","banana"]               | array   |
| false                            | boolean |
+----------------------------------+---------+

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 ou TO_JSON.

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	(TO_JSON_STRING uniquement) nombre ou chaîne Encodé sous forme de nombre lorsque la valeur est comprise dans la plage [-2^53, 2^53], 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"
INT64	(TO_JSON uniquement) nombre ou chaîne Si l'argument stringify_wide_numbers est TRUE et que la valeur se situe en dehors du domaine de type FLOAT64, la valeur est encodée au format chaîne. Si la valeur ne peut pas être stockée au format JSON sans perte de précision, la fonction échoue. Dans le cas contraire, cette valeur est encodée sous forme de nombre. Si stringify_wide_numbers n'est pas utilisé ou est défini sur FALSE, les valeurs numériques situées en dehors du domaine de type "FLOAT64" ne sont pas encodées en tant que chaînes, mais stockées sous forme de nombres JSON. Si une valeur numérique ne peut pas être stockée au format JSON sans perte de précision, une erreur est générée.	Entrée SQL : 9007199254740992 Sortie JSON : 9007199254740992 Entrée SQL : 9007199254740993 Sortie JSON : 9007199254740993 Entrée SQL avec stringify_wide_numbers=>TRUE :9007199254740992 Sortie JSON : 9007199254740992 Entrée SQL avec stringify_wide_numbers=>TRUE : 9007199254740993 Sortie JSON : "9007199254740993"
NUMERIC BIGNUMERIC	(TO_JSON_STRING uniquement) nombre ou chaîne Encodé sous forme de nombre lorsque la valeur est comprise dans la plage [-2^53, 2^53] 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"
NUMERIC BIGNUMERIC	(TO_JSON uniquement) nombre ou chaîne Si l'argument stringify_wide_numbers est TRUE et que la valeur se situe en dehors du domaine de type FLOAT64, il est encodé sous forme de chaîne. Dans le cas contraire, la valeur est encodée sous forme de nombre.	Entrée SQL : -1 Sortie JSON : -1 Entrée SQL : 0 Sortie JSON : 0 Entrée SQL : 9007199254740993 Sortie JSON : 9007199254740993 Entrée SQL : 123.56 Sortie JSON : 123.56 Entrée SQL avec stringify_wide_numbers=>TRUE : 9007199254740993 Sortie JSON : "9007199254740993" Entrée SQL avec stringify_wide_numbers=>TRUE : 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"
JSON	données de l'entrée JSON	Entrée SQL : JSON '{"item": "pen", "price": 10}' Sortie JSON : {"item":"pen", "price":10} Entrée SQL : [1, 2, 3] Sortie JSON : [1, 2, 3]
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, un champ est inclus dans la chaîne de sortie et tous les doublons de ce champ sont omis. 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}

Format JSONPath

Avec le format JSONPath, vous pouvez identifier les valeurs que vous souhaitez obtenir à partir d'une chaîne au format JSON. Le format JSONPath est compatible avec les opérateurs suivants:

Opérateur	Description	Exemples
$	Objet ou élément racine Le format JSONPath doit commencer par cet opérateur, qui fait référence au niveau le plus externe (le plus haut niveau) de la chaîne au format JSON.	Chaîne au format JSON : '{"class" : {"students" : [{"name" : "Jane"}]}}' Chemin d'accès JSON : "$" Résultat JSON : {"class":{"students":[{"name":"Jane"}]}}
.	Opérateur enfant Vous pouvez identifier les valeurs enfants à l'aide de la notation par points.	Chaîne au format JSON : '{"class" : {"students" : [{"name" : "Jane"}]}}' Chemin d'accès JSON : "$.class.students" Résultat JSON : [{"name":"Jane"}]
[]	Opérateur indice. Si l'objet JSON est un tableau, vous pouvez utiliser des crochets pour spécifier l'index de tableau.	Chaîne au format JSON : '{"class" : {"students" : [{"name" : "Jane"}]}}' Chemin d'accès JSON : "$.class.students[0]" Résultat JSON : {"name":"Jane"}

Si une clé d'une fonction JSON contient un opérateur au format JSON, reportez-vous à chaque fonction JSON pour savoir comment les échapper.

Une fonction JSON renvoie NULL si le format JSONPath ne correspond pas à une valeur d'une chaîne au format JSON. 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, une erreur est générée.