BigQuery admite funciones que te ayudan a recuperar datos almacenados en strings con formato JSON y funciones que te ayudan a transformar datos en strings con formato JSON.
JSON_EXTRACT o JSON_EXTRACT_SCALAR
JSON_EXTRACT(json_string_expr,
json_path_format)
, que muestra valores JSON como STRING.
JSON_EXTRACT_SCALAR(json_string_expr,
json_path_format)
, que muestra valores JSON escalares como STRING.
Descripción
Extrae valores JSON o valores escalares JSON como strings.
json_string_expr
: Una string con formato JSON. Por ejemplo:{"class" : {"students" : [{"name" : "Jane"}]}}
json_path_format
: El formato JSONpath. Esto identifica el valor o los valores que deseas obtener de la string con formato JSON. Sijson_path_format
muestra unnull
de JSON, este se convierte en unNULL
de SQL.
En los casos en los que una clave JSON usa caracteres JSONPath no válidos, puedes escapar esos caracteres mediante el uso de comillas simples y corchetes.
Tipo de datos que se muestra
STRING
Ejemplos
SELECT JSON_EXTRACT(json_text, '$') AS json_text_string
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------------------------------------------------+
| json_text_string |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}} |
| {"class":{"students":[]}} |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------+
| first_student |
+-----------------+
| {"name":"Jane"} |
| NULL |
| {"name":"John"} |
+-----------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-------------------+
| second_student |
+-------------------+
| NULL |
| NULL |
| NULL |
| "Jamie" |
+-------------------+
SELECT JSON_EXTRACT(json_text, "$.class['students']") AS student_names
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+------------------------------------+
| student_names |
+------------------------------------+
| [{"name":"Jane"}] |
| [] |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;
+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob" | Jakob | "6" | 6 |
+-----------+-------------+----------+--------+
En los casos en los que una clave JSON usa caracteres JSONPath no válidos, puedes escapar esos caracteres mediante el uso de comillas simples y corchetes, [' ']
. Por ejemplo:
SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}', "$['a.b'].c") as hello;
+-------+
| hello |
+-------+
| world |
+-------+
JSON_EXTRACT_ARRAY
JSON_EXTRACT_ARRAY(json_string_expr[, json_path_format])
Descripción
Extrae un arreglo de una string con formato JSON.
json_string_expr
: Una string con formato JSON. Por ejemplo:{"class" : {"students" : [{"name" : "Jane"}]}}
json_path_format
: El formato JSONpath. Esto identifica el valor o los valores que deseas obtener de la string con formato JSON. Si no se proporciona este parámetro opcional, se aplica el símbolo$
de JSONPath, lo que significa que se analiza toda la string con formato JSON.
En los casos en los que una clave JSON usa caracteres JSONPath no válidos, puedes escapar esos caracteres mediante el uso de comillas simples y corchetes.
Tipo de datos que se muestra
ARRAY<STRING>
Ejemplos
En este ejemplo, se extraen los elementos de una string con formato JSON en un arreglo de strings:
SELECT JSON_EXTRACT_ARRAY('[1,2,3]') as string_array
+----------------+
| string_array |
+----------------+
| ['1','2','3'] |
+----------------+
En este ejemplo, se extrae un arreglo de strings y se lo convierte en un arreglo de números enteros:
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] |
+---------------+
En este ejemplo, se extraen los valores de string de una string con formato JSON en un arreglo:
-- Don't strip the double quotes
SELECT JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]', '$') as string_array
+--------------------------------------+
| string_array |
+--------------------------------------+
| ['"apples"','"oranges"','"grapes"'] |
+--------------------------------------+
-- Strip the double quotes
SELECT ARRAY(
SELECT JSON_EXTRACT_SCALAR(string_element, '$')
FROM UNNEST(JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array
+---------------------------------+
| string_array |
+---------------------------------+
| ['apples', 'oranges', 'grapes'] |
+---------------------------------+
En este ejemplo, se extraen solo los elementos de fruit
en un arreglo:
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}'] |
+----------------------------------------------------------------------+
Estos son equivalentes:
JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') as string_array
JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') as string_array
-- The queries above produce this result:
+---------------------------------------+
| string_array |
+---------------------------------------+
| [""apples"", ""oranges"", ""grapes""] |
+---------------------------------------+
En los casos en los que una clave JSON usa caracteres JSONPath no válidos, puedes escapar esos caracteres mediante el uso de comillas simples y corchetes, [' ']
. Por ejemplo:
SELECT JSON_EXTRACT_ARRAY('{"a.b": {"c": "world"}}', "$['a.b'].c") as hello;
+---------+
| hello |
+---------+
| [world] |
+---------+
En estos ejemplos, se analiza cómo se manejan las solicitudes no válidas y los arreglos vacíos:
- Si un JSONPath no es válido, se produce un error.
- Si una string con formato JSON no es válida, el resultado es NULL.
- Se permite tener arreglos vacíos en la string con formato JSON.
-- An error is thrown if you provide an invalid JSONPath.
JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') as result
-- If a key is not specified when a key is expected, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$') as result
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a key that does not exist is specified, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$.b') as result
+--------+
| result |
+--------+
| NULL |
+--------+
-- Empty arrays in JSON-formatted strings are supported.
JSON_EXTRACT_ARRAY('{"a":"foo","b":[]}','$.b') as result
+--------+
| result |
+--------+
| [] |
+--------+
JSON_QUERY o JSON_VALUE
JSON_QUERY(json_string_expr, json_path_format)
, que muestra valores JSON como STRING.
JSON_VALUE(json_string_expr, json_path_format)
, que muestra valores JSON escalares como STRING.
Descripción
Extrae valores JSON o valores escalares JSON como strings.
json_string_expr
: Una string con formato JSON. Por ejemplo:{"class" : {"students" : [{"name" : "Jane"}]}}
json_path_format
: El formato JSONpath. Esto identifica el valor o los valores que deseas obtener de la string con formato JSON. Sijson_path_format
muestra unnull
de JSON, este se convierte en unNULL
de SQL.
En los casos en que una clave JSON use caracteres JSONPath no válidos, puedes escapar esos caracteres mediante comillas dobles.
Ejemplos
SELECT JSON_QUERY(json_text, '$') AS json_text_string
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------------------------------------------------+
| json_text_string |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}} |
| {"class":{"students":[]}} |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_QUERY(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------+
| first_student |
+-----------------+
| {"name":"Jane"} |
| NULL |
| {"name":"John"} |
+-----------------+
SELECT JSON_QUERY(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-------------------+
| second_student |
+-------------------+
| NULL |
| NULL |
| NULL |
| "Jamie" |
+-------------------+
SELECT JSON_QUERY(json_text, '$.class."students"') AS student_names
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+------------------------------------+
| student_names |
+------------------------------------+
| [{"name":"Jane"}] |
| [] |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;
+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob" | Jakob | "6" | 6 |
+-----------+-------------+----------+--------+
En los casos en que una clave JSON use caracteres JSONPath no válidos, puedes escapar esos caracteres mediante comillas dobles. Por ejemplo:
SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') as hello;
+-------+
| hello |
+-------+
| world |
+-------+
TO_JSON_STRING
TO_JSON_STRING(value[, pretty_print])
Descripción
Muestra una representación de una string con formato JSON de value
. Esta función admite un parámetro booleano opcional llamado pretty_print
. Si pretty_print
es true
, el valor que se muestre tendrá un formato para una lectura fácil.
Tipo de datos de entrada | Valor mostrado |
---|---|
NULL de cualquier tipo | null |
BOOL | true o false . |
INT64 | Lo mismo que -1 0 12345678901 9007199254740992 -9007199254740992 "9007199254740993"
|
NUMERIC | Igual que -1 0 "9007199254740993" "123.56"
|
FLOAT64 | +/-inf y NaN se representan como Infinity , -Infinity y NaN , respectivamente.De lo contrario, es lo mismo que |
STRING | Valor de string entre comillas, evitado según el estándar JSON.
En específico, se escapan " , \ y los caracteres de control de U+0000 a U+001F . |
BYTES | Valor evitado RFC 4648 de base64. Por ejemplo:
|
DATE | Fecha entre comillas. Por ejemplo: "2017-03-06"
|
TIMESTAMP | Fecha y hora ISO 8601 entre comillas, donde T separa la fecha y hora y Zulu/UTC representa la zona horaria. Por ejemplo: "2017-03-06T12:34:56.789012Z"
|
DATETIME | Fecha y hora ISO 8601 entre comillas, donde T separa la fecha y hora. Por ejemplo: "2017-03-06T12:34:56.789012"
|
TIME | Hora ISO 8601 entre comillas. Por ejemplo: "12:34:56.789012" |
ARRAY |
Arreglo de cero o más elementos. Cada elemento tiene un formato según su tipo. Ejemplo sin formato: ["red", "blue", "green"] Ejemplo con formato: [ "red", "blue", "green" ] |
STRUCT |
Un objeto que contiene cero o más pares clave-valor. Cada valor tiene un formato según su tipo. Ejemplo sin formato: {"colors":["red","blue"],"purchases":12,"inStock": true} Ejemplo con formato: { "color":[ "red", "blue" ] "purchases":12, "inStock": true }
Los campos con nombres duplicados pueden dar como resultado JSON no analizable. Los campos anónimos se representan con
Los nombres de campo UTF-8 no válidos pueden dar como resultado JSON no analizable. Los valores de string se escapan según el estándar JSON. En específico, se escapan |
Tipo de datos que se muestra
Representación de la string JSON del valor.
Ejemplos
Convierte las filas en una tabla a 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]} |
+--------+-------------+--------------------------------+
Convierte las filas en una tabla a JSON con formato.
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 |
| | | ] |
| | | } |
+--------+-------------+---------------------+
Formato JSONPath
La mayoría de las funciones JSON pasan en un parámetro json_string_expr
y json_path_format
. El parámetro json_string_expr
pasa una string con formato JSON y el parámetro json_path_format
identifica el valor o los valores que deseas obtener de la string con formato JSON.
El parámetro json_string_expr
debe ser una string JSON con el siguiente formato:
{"class" : {"students" : [{"name" : "Jane"}]}}
Crea el parámetro json_path_format
mediante el formato JSONPath. Como parte de este formato, este parámetro debe comenzar con un símbolo $
, que hace referencia al nivel más externo de la string con formato JSON. Puedes identificar los valores secundarios mediante puntos. Si el objeto JSON es un arreglo, puedes usar corchetes para especificar el índice del arreglo. Si las claves contienen $
, puntos o corchetes, consulta cada función JSON para saber cómo escaparlas.
JSONPath | Descripción | Ejemplo | Resultado mediante el json_string_expr anterior |
---|---|---|---|
$ | Objeto o elemento raíz | “$” | {"class":{"students":[{"name":"Jane"}]}} |
. | Operador secundario | “$.class.students” | [{"name":"Jane"}] |
[] | Operador subíndice | “$.class.students[0]” | {"name":"Jane"} |
Una función JSON muestra NULL
si el parámetro json_path_format
no coincide con un valor en json_string_expr
. Si el valor seleccionado para una función escalar no es escalar, como un objeto o un arreglo, la función muestra NULL
.
Si JSONPath no es válido, la función genera un error.