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. Construyes 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 el uso de 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 produce el siguiente resultado:

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

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

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| {"first":"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 produce el siguiente resultado:

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

+-----------+-------------+----------+--------+
| 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 [-253, 253], 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 253, por lo que se representa como una string entre comillas.

NUMERIC

Igual que CAST(value AS STRING) cuando value está en el rango de [-253, 253] 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 produce 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 produce 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"  |
|  }                    |
|}                      |
+-----------------------+
¿Te sirvió esta página? Envíanos tu opinión:

Enviar comentarios sobre…

¿Necesitas ayuda? Visita nuestra página de asistencia.