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

El parámetro json_string_expr debe ser una string con formato JSON. Por ejemplo:

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

El parámetro json_path_string_literal identifica el valor o los valores que deseas obtener de la string con formato JSON. Tú debes construir este parámetro mediante el uso del 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 valores secundarios mediante la notación de puntos o corchetes. Si el objeto JSON es un arreglo, puedes usar corchetes para especificar el índice del arreglo.

JSONPath	Descripción
$	Objeto o elemento raíz
. o []	Operador secundario
[]	Operador subíndice

Ambas funciones muestran NULL si el parámetro json_path_string_literal no coincide con un valor en json_string_expr. Si el valor seleccionado para JSON_EXTRACT_SCALAR no es escalar, como un objeto o un arreglo, la función muestra NULL.

Si JSONPath no es válido, estas funciones generan un error.

En los casos en los que una clave JSON utiliza caracteres JSONPath no válidos, puedes marcar 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 |
+-------+

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;

La consulta anterior arroja el resultado siguiente:

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

La consulta anterior arroja el resultado siguiente:

+-----------------+
| 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": "Jamie"}]}}'
  ]) AS json_text;

La consulta anterior arroja el resultado siguiente:

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

La consulta anterior arroja el resultado siguiente:

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

La consulta anterior arroja el resultado siguiente:

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

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	Igual 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érdida como números de punto flotante de doble precisión 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, igual que `CAST(value AS STRING)`.
STRING	Valor de string entre comillas, evitado según el estándar JSON. Específicamente, se evitan `"`, `\` 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 base64 de 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,...]`, donde cada `elem` está formateado 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` está formateado según su tipo. Ejemplo con formato: { "field_name1": field_value1, "field_name2": field_value2, ... } Donde cada `field_value` está formateado 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 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 evitan según el estándar JSON. Específicamente, se evitan `"`, `\` y los caracteres de control de `U+0000` a `U+001F`.

Tipo de devolución

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 resultado siguiente:

+-----------------------------------+-------------------------------------------------------+
| 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 resultado siguiente:

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