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_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. Si json_path_format 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_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. Si json_path_format muestra un null de JSON, este se convierte en un NULL 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 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é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 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, 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

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 "". Si un campo tiene un arreglo o un objeto no vacío, los elementos o campos tendrán una sangría al nivel adecuado.

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

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.