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 JSONnull
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'unnull
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 JSONjson_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 JSONnull
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'unnull
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 JSONjson_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 JSONnull
ou une valeur non scalaire (en d'autres termes, sijson_path
fait référence à un objet ou à un tableau), un résultat SQLNULL
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 JSONnull
ou une valeur non scalaire (en d'autres termes, sijson_path
fait référence à un objet ou à un tableau), un résultat SQLNULL
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 SQLNULL
en tant que sortie pour cette valeur JSONnull
. Si la sortie contient un élément de tableauNULL
, une erreur est générée, car le résultat final ne peut pas être un tableau avec des valeursNULL
. - 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 valeursNULL
.
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 SQLNULL
en sortie pour la valeur JSONnull
. Si la sortie contient un élément de tableauNULL
, une erreur est générée, car le résultat final ne peut pas être un tableau avec des valeursNULL
. - 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 valeursNULL
.
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
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
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 typeFLOAT64
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 domaineFLOAT64
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
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
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
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
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
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 [-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" |
INT64 |
(TO_JSON uniquement) nombre ou chaîne
Si l'argument
Si |
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 [-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" |
NUMERIC BIGNUMERIC |
(TO_JSON uniquement) nombre ou chaîne
Si l'argument |
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
|
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, |
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
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, |
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 :
Chemin d'accès JSON :
Résultat JSON : |
. |
Opérateur enfant Vous pouvez identifier les valeurs enfants à l'aide de la notation par points. |
Chaîne au format JSON :
Chemin d'accès JSON :
Résultat JSON : |
[] |
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 :
Chemin d'accès JSON :
Résultat JSON : |
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.