Funciones JSON en SQL estándar

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_string_literal), que muestra valores JSON como STRING.

JSON_EXTRACT_SCALAR(json_string_expr, json_path_string_literal), 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_string_literal: El formato JSONpath. Esto identifica el valor o los valores que deseas obtener de la string con formato JSON. Si json_path_string_literal muestra un null de JSON, este se convierte en un NULL 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_string_literal])

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

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 pretty_print opcional. Si pretty_print está presente, el valor que se muestra tiene 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 `CAST(value AS STRING)` cuando `value` está en el rango de [-2⁵³, 2⁵³], que es el rango de números enteros que se pueden representar sin pérdidas como números de puntos flotante de precisión doble IEEE 754. Los valores fuera de este rango se representan como strings entre comillas. Por ejemplo: `-1` `0` `12345678901` `9007199254740992` `-9007199254740992` `"9007199254740993"` `9007199254740993` es mayor que 2⁵³, por lo que se representa como una string entre comillas.
NUMERIC	Igual que `CAST(value AS STRING)` cuando `value` está en el rango de [-2⁵³, 2⁵³] y no tiene parte fraccionaria. Los valores fuera de este rango se representan como strings entre comillas. Por ejemplo: `-1` `0` `"9007199254740993"` `"123.56"`
FLOAT64	`+/-inf` y `NaN` se representan como `Infinity`, `-Infinity` y `NaN`, respectivamente. De lo contrario, es lo mismo que `CAST(value AS STRING)`.
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: `"R29vZ2xl"` es la representación en base64 de los bytes `b"Google"`
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	`[elem1,elem2,...]`, en el que cada `elem` se formatea según el tipo de elemento. Ejemplo con formato: [ elem1, elem2, ... ] Donde cada elem está formateado según el tipo de elemento. El arreglo vacío se representa como `[]`.
STRUCT	`{"field_name1":field_value1,"field_name2":field_value2,...}` Donde cada `field_value` se formatea según su tipo. Ejemplo con formato: { "field_name1": field_value1, "field_name2": field_value2, ... } Donde cada `field_value` se formatea según su tipo. Si un `field_value` es un ARRAY o STRUCT que no está vacío, los elementos se sangran al nivel adecuado. El struct vacío se representa como `{}`. 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 `"`, `\` y los caracteres de control de `U+0000` a `U+001F`.

Tipo de datos que se muestra

Representación de la string JSON del valor.

Ejemplos

Convertir las filas en una tabla a JSON.

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  t,
  TO_JSON_STRING(t) AS json_row
FROM Input AS t;

La consulta anterior arroja el siguiente resultado:

+-----------------------------------+-------------------------------------------------------+
| t                                 | json_row                                              |
+-----------------------------------+-------------------------------------------------------+
| {[1, 2], foo, {true, 2017-04-05}} | {"x":[1,2],"y":"foo","s":{"a":true,"b":"2017-04-05"}} |
| {NULL, , {false, 0001-01-01}}     | {"x":null,"y":"","s":{"a":false,"b":"0001-01-01"}}    |
| {[3], bar, {NULL, 2016-12-05}}    | {"x":[3],"y":"bar","s":{"a":null,"b":"2016-12-05"}}   |
+-----------------------------------+-------------------------------------------------------+

Convertir filas en una tabla a JSON con formato.

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  TO_JSON_STRING(t, true) AS json_row
FROM Input AS t;

La consulta anterior arroja el siguiente resultado:

+-----------------------+
| json_row              |
+-----------------------+
| {                     |
|  "x": [               |
|    1,                 |
|    2                  |
|  ],                   |
|  "y": "foo",          |
|  "s": {               |
|    "a": true,         |
|    "b": "2017-04-05"  |
|  }                    |
|}                      |
| {                     |
|  "x": null,           |
|  "y": "",             |
|  "s": {               |
|    "a": false,        |
|    "b": "0001-01-01"  |
|  }                    |
|}                      |
| {                     |
|  "x": [               |
|    3                  |
|  ],                   |
|  "y": "bar",          |
|  "s": {               |
|    "a": null,         |
|    "b": "2016-12-05"  |
|  }                    |
|}                      |
+-----------------------+

Formato JSONPath

La mayoría de las funciones JSON pasan en un parámetro json_string_expr y json_path_string_literal. El parámetro json_string_expr pasa una string con formato JSON y el parámetro json_path_string_literal 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_string_literal 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_string_literal 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.