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.

Descripción general de la función

Las siguientes funciones usan comillas dobles para escapar los caracteres de JSONPath no válidos: "a.b".

Este comportamiento es coherente con el estándar ANSI.

Función JSON Descripción Tipo de datos que se muestra
JSON_QUERY Extrae un valor JSON, como un objeto o un array, o un valor escalar con formato JSON, como una string, un número entero o un valor booleano. STRING con formato JSON
JSON_VALUE Extrae un valor escalar. Un valor escalar puede representar una string, un número entero o un valor booleano. Quita las comillas más externas y el escape de los valores. Muestra un NULL de SQL si se selecciona un valor no escalar. STRING
JSON_QUERY_ARRAY Extrae un array de valores JSON, como objetos o arrays, y valores escalares en formato JSON, como strings, números enteros y booleanos. ARRAY<JSON-formatted STRING>
JSON_VALUE_ARRAY Extrae un arreglo de valores escalares. Un valor escalar puede representar una string, un número entero o un valor booleano. Quita las comillas más externas y el escape de los valores. Muestra un NULL de SQL si el valor seleccionado no es un arreglo o no un arreglo que solo contiene valores escalares. ARRAY<STRING>

Funciones de extracción de JSON heredadas

Las siguientes funciones usan comillas simples y corchetes para escapar caracteres de JSONPath no válidos: ['a.b'].

Si bien estas funciones son compatibles con BigQuery, recomendamos usar las funciones de la tabla anterior.

Función JSON Descripción Tipo de datos que se muestra
JSON_EXTRACT Extrae un valor JSON, como un objeto o un array, o un valor escalar con formato JSON, como una string, un número entero o un valor booleano. STRING con formato JSON
JSON_EXTRACT_SCALAR Extrae un valor escalar. Un valor escalar puede representar una string, un número entero o un valor booleano. Quita las comillas más externas y el escape de los valores. Muestra un NULL de SQL si se selecciona un valor no escalar. STRING
JSON_EXTRACT_ARRAY Extrae un array de valores JSON, como objetos o arrays, y valores escalares en formato JSON, como strings, números enteros y booleanos. ARRAY<JSON-formatted STRING>
JSON_EXTRACT_STRING_ARRAY Extrae un arreglo de valores escalares. Un valor escalar puede representar una string, un número entero o un valor booleano. Quita las comillas más externas y el escape de los valores. Muestra un NULL de SQL si el valor seleccionado no es un arreglo o no un arreglo que solo contiene valores escalares. ARRAY<STRING>

Otras funciones de JSON

Función JSON Descripción Tipo de datos que se muestra
TO_JSON_STRING Toma un valor SQL y muestra una representación de string con formato JSON del valor. STRING con formato JSON

JSON_EXTRACT

JSON_EXTRACT(json_string_expr, json_path)

Descripción

Extrae un valor JSON, como un array o un objeto, o un valor escalar con formato JSON, como una string, un número entero o un valor booleano. Si una clave JSON usa caracteres JSONPath no válidos, puedes marcar esos caracteres mediante el uso de comillas simples y corchetes.

  • json_string_expr: Una string con formato JSON. Por ejemplo:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path: El JSONPath. Esto identifica el valor o los valores que deseas obtener de la string con formato JSON. Si json_path muestra un null de JSON, entonces, este se convierte en un NULL de SQL.

Si deseas incluir valores no escalares, como arrays en la extracción, usa JSON_EXTRACT. Si solo deseas extraer valores escalares, como strings, números enteros y booleanos, usa JSON_EXTRACT_SCALAR.

Tipo de datos que se muestra

Un STRING con formato JSON

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

JSON_QUERY

JSON_QUERY(json_string_expr, json_path)

Descripción

Extrae un valor JSON, como un array o un objeto, o un valor escalar con formato JSON, como una string, un número entero o un valor booleano. Si una clave JSON usa caracteres JSONPath no válidos, puedes marcar esos caracteres mediante comillas dobles.

  • json_string_expr: Una string con formato JSON. Por ejemplo:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path: El JSONPath. Esto identifica el valor o los valores que deseas obtener de la string con formato JSON. Si json_path muestra un null de JSON, entonces, este se convierte en un NULL de SQL.

Si deseas incluir valores no escalares, como arrays en la extracción, usa JSON_QUERY. Si solo deseas extraer valores escalares, como strings, números enteros y booleanos, usa JSON_VALUE.

Tipo de datos que se muestra

Un STRING con formato JSON

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

JSON_EXTRACT_SCALAR

JSON_EXTRACT_SCALAR(json_string_expr[, json_path])

Descripción

Extrae un valor escalar y lo muestra como una string. Un valor escalar puede representar una string, un número entero o un valor booleano. Quita las comillas más externas y el escape de los valores que se muestran. Si una clave JSON usa caracteres JSONPath no válidos, puedes marcar esos caracteres mediante el uso de comillas simples y corchetes.

  • json_string_expr: Una string con formato JSON. Por ejemplo:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path: El JSONPath. Esto identifica el valor o los valores que deseas obtener de la string con formato JSON. Si json_path muestra un null de JSON o un valor no escalar (en otras palabras, si json_path hace referencia a un objeto o un array) y, luego, un SQLNULL. 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.

Si solo deseas extraer valores escalares, como strings, números enteros y booleanos, usa JSON_EXTRACT_SCALAR. Si deseas incluir valores no escalares, como arrays en la extracción, usa JSON_EXTRACT.

Tipo de datos que se muestra

STRING

Ejemplos

En el siguiente ejemplo, se compara cómo se muestran los resultados para las funciones JSON_EXTRACT y JSON_EXTRACT_SCALAR.

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

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

SELECT JSON_EXTRACT('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract,
  JSON_EXTRACT_SCALAR('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract_scalar;

+--------------------+---------------------+
| json_extract       | json_extract_scalar |
+--------------------+---------------------+
| ["apple","banana"] | NULL                |
+--------------------+---------------------+

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_VALUE

JSON_VALUE(json_string_expr[, json_path])

Descripción

Extrae un valor escalar y lo muestra como una string. Un valor escalar puede representar una string, un número entero o un valor booleano. Quita las comillas más externas y el escape de los valores que se muestran. Si una clave JSON usa caracteres JSONPath no válidos, puedes marcar esos caracteres mediante comillas dobles.

  • json_string_expr: Una string con formato JSON. Por ejemplo:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path: El JSONPath. Esto identifica el valor o los valores que deseas obtener de la string con formato JSON. Si json_path muestra un null de JSON o un valor no escalar (en otras palabras, si json_path hace referencia a un objeto o un array) y, luego, un SQLNULL. 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.

Si solo deseas extraer valores escalares, como strings, números enteros y booleanos, usa JSON_VALUE. Si deseas incluir valores no escalares, como arrays en la extracción, usa JSON_QUERY.

Tipo de datos que se muestra

STRING

Ejemplos

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

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

SELECT JSON_QUERY('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_query,
  JSON_VALUE('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_value;

+--------------------+------------+
| json_query         | json_value |
+--------------------+------------+
| ["apple","banana"] | NULL       |
+--------------------+------------+

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

JSON_EXTRACT_ARRAY

JSON_EXTRACT_ARRAY(json_string_expr[, json_path])

Descripción

Extrae un array de valores JSON, como objetos o arrays, y valores escalares en formato JSON, como strings, números enteros y booleanos. Si una clave JSON usa caracteres JSONPath no válidos, puedes marcar esos caracteres mediante el uso de comillas simples y corchetes.

  • json_string_expr: Una string con formato JSON. Por ejemplo:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path: El 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.

Tipo de datos que se muestra

ARRAY<JSON-FORMATTED 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:

-- Doesn't strip the double quotes
SELECT JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]', '$') AS string_array;

+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

-- Strips 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] |
+---------------------------+

Esto extrae solo los elementos de la propiedad fruit a un array:

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:

SELECT JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') AS string_array;

SELECT JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

-- The queries above produce the following 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 los siguientes ejemplos, se explora cómo se manejan las solicitudes no válidas y los arrays 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.
SELECT JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a key that does not exist is specified, then the result is NULL.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo"}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- Empty arrays in JSON-formatted strings are supported.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

JSON_QUERY_ARRAY

JSON_QUERY_ARRAY(json_string_expr[, json_path])

Descripción

Extrae un array de valores JSON, como objetos o arrays, y valores escalares en formato JSON, como strings, números enteros y booleanos. Si una clave JSON usa caracteres JSONPath no válidos, puedes marcar esos caracteres mediante comillas dobles.

  • json_string_expr: Una string con formato JSON. Por ejemplo:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path: El 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.

Tipo de datos que se muestra

ARRAY<JSON-FORMATTED STRING>

Ejemplos

En este ejemplo, se extraen los elementos de una string con formato JSON en un arreglo de strings:

SELECT JSON_QUERY_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_QUERY_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:

-- Doesn't strip the double quotes
SELECT JSON_QUERY_ARRAY('["apples","oranges","grapes"]', '$') AS string_array;

+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

-- Strips the double quotes
SELECT ARRAY(
  SELECT JSON_EXTRACT_SCALAR(string_element, '$')
  FROM UNNEST(JSON_QUERY_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array;

+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

Esto extrae solo los elementos de la propiedad fruit a un array:

SELECT JSON_QUERY_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:

SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_array;

-- The queries above produce the following result:
+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

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_QUERY_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello;

+-----------+
| hello     |
+-----------+
| ["world"] |
+-----------+

En los siguientes ejemplos, se muestra cómo se manejan las solicitudes no válidas y los arrays vacíos:

-- An error is returned if you provide an invalid JSONPath.
SELECT JSON_QUERY_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a key that does not exist is specified, then the result is NULL.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- Empty arrays in JSON-formatted strings are supported.
SELECT JSON_QUERY_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

JSON_EXTRACT_STRING_ARRAY

JSON_EXTRACT_STRING_ARRAY(json_string_expr[, json_path])

Descripción

Extrae un arreglo de valores escalares y muestra un arreglo de valores escalares con formato de string. Un valor escalar puede representar una string, un número entero o un valor booleano. Si una clave JSON usa caracteres JSONPath no válidos, puedes escapar esos caracteres mediante el uso de comillas simples y corchetes.

  • json_string_expr: Una string con formato JSON. Por ejemplo:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path: El 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.

Tipo de datos que se muestra

ARRAY<STRING>

Ejemplos

En el siguiente ejemplo, se compara cómo se muestran los resultados para las funciones JSON_EXTRACT_ARRAY y JSON_EXTRACT_STRING_ARRAY.

SELECT JSON_EXTRACT_ARRAY('["apples","oranges"]') AS json_array,
JSON_EXTRACT_STRING_ARRAY('["apples","oranges"]') AS string_array;

+-----------------------+-------------------+
| json_array            | string_array      |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+

En este ejemplo, se extraen los elementos de una string con formato JSON en un arreglo de strings:

-- Strips the double quotes
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','$') AS string_array;

+-------------------+
| string_array      |
+-------------------+
| [foo, bar, baz]   |
+-------------------+

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_STRING_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

+---------------+
| integer_array |
+---------------+
| [1, 2, 3]     |
+---------------+

Estos son equivalentes:

SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') AS string_array;

SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

-- The queries above produce the following 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_STRING_ARRAY('{"a.b": {"c": ["world"]}}', "$['a.b'].c") AS hello;

+---------+
| hello   |
+---------+
| [world] |
+---------+

En los siguientes ejemplos, se explora cómo se manejan las solicitudes no válidas y los arrays vacíos:

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSON formatted string is invalid, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY('}}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY(NULL,'$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo"}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of mixed scalar and non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

-- If a JSONPath matches an array that contains scalar values and a JSON null,
-- then the output of the JSON_EXTRACT_STRING_ARRAY function must be transformed
-- because the final output cannot be an array with NULL values. This example
-- uses the UNNEST operator to convert the output array into a table as the final output.
SELECT string_value FROM UNNEST(JSON_EXTRACT_STRING_ARRAY('["world", 1, null]')) AS string_value;

+--------------+
| string_value |
+--------------+
| world        |
| 1            |
| NULL         |
+--------------+

JSON_VALUE_ARRAY

JSON_VALUE_ARRAY(json_string_expr[, json_path])

Descripción

Extrae un arreglo de valores escalares y muestra un arreglo de valores escalares con formato de string. Un valor escalar puede representar una string, un número entero o un valor booleano. Si una clave JSON usa caracteres JSONPath no válidos, puedes escapar de esos caracteres mediante comillas dobles.

  • json_string_expr: Una string con formato JSON. Por ejemplo:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path: El 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.

Tipo de datos que se muestra

ARRAY<STRING>

Ejemplos

En el siguiente ejemplo, se compara cómo se muestran los resultados para las funciones JSON_QUERY_ARRAY y JSON_VALUE_ARRAY.

SELECT JSON_QUERY_ARRAY('["apples","oranges"]') AS json_array,
       JSON_VALUE_ARRAY('["apples","oranges"]') AS string_array;

+-----------------------+-------------------+
| json_array            | string_array      |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+

En este ejemplo, se extraen los elementos de una string con formato JSON en un arreglo de strings:

-- Strips the double quotes
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','$') AS string_array;

+-----------------+
| string_array    |
+-----------------+
| [foo, bar, baz] |
+-----------------+

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_VALUE_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

+---------------+
| integer_array |
+---------------+
| [1, 2, 3]     |
+---------------+

Estos son equivalentes:

SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;
SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_array;

-- The queries above produce the following result:
+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

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_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello;

+---------+
| hello   |
+---------+
| [world] |
+---------+

En los siguientes ejemplos, se explora cómo se manejan las solicitudes no válidas y los arrays vacíos:

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSON-formatted string is invalid, then NULL is returned.
SELECT JSON_VALUE_ARRAY('}}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_VALUE_ARRAY(NULL,'$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo"}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of mixed scalar and non-scalar objects,
-- then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

-- If a JSONPath matches an array that contains scalar objects and a JSON null,
-- then the output of the JSON_VALUE_ARRAY function must be transformed
-- because the final output cannot be an array with NULL values. This example
-- uses the UNNEST operator to convert the output array into a table as the final output.
SELECT string_value FROM UNNEST(JSON_VALUE_ARRAY('["world", 1, null]')) AS string_value;

+--------------+
| string_value |
+--------------+
| world        |
| 1            |
| NULL         |
+--------------+

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

Descripción

Toma un valor SQL y muestra una representación de string con formato JSON del valor. El valor debe ser un tipo de datos compatible con BigQuery. Puedes revisar los tipos de datos de BigQuery que admite esta función y sus codificaciones JSON aquí.

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 que se muestra

Un STRING con formato JSON

Ejemplos

Convertir filas en una tabla a strings con formato 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 filas en una tabla en strings con formato JSON que sean fáciles de leer.

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

Codificaciones de JSON

En la siguiente tabla, se incluyen codificaciones comunes que se usan cuando un valor de SQL está codificado como valor JSON con la función TO_JSON_STRING.

Desde SQL A JSON Ejemplos
NULL

null

Entrada de SQL: NULL
Resultado de JSON: null
BOOL booleano Entrada de SQL: TRUE
Resultado de JSON: true

Entrada de SQL: FALSE
Resultado de JSON: false
INT64

número o string

Se codifica como un número cuando el valor 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. Un valor fuera de este rango se codifica como una string.

Entrada de SQL: 9007199254740992
Resultado de JSON: 9007199254740992

Entrada de SQL: 9007199254740993
Resultado de JSON: "9007199254740993"
NUMERIC
BIGNUMERIC

número o string

Se codifica como un número cuando el valor está en el rango de [-253, 253] y no hay una parte fraccionaria. Un valor fuera de este rango se codifica como una string.

Entrada de SQL: -1
Salida de JSON: -1

Entrada de SQL: 0
Resultado de JSON: 0

Entrada de SQL: 9007199254740993
Resultado de JSON: "9007199254740993"

Entrada de SQL: 123.56
Resultado de JSON: "123.56"
FLOAT64

número o string

+/-inf y NaN se codifican como Infinity, -Infinity y NaN. De lo contrario, este valor se codifica como una string.

Entrada de SQL: 1.0
Resultado de JSON: 1

Entrada de SQL: 9007199254740993
Resultado de JSON: 9007199254740993

Entrada de SQL: "+inf"
Resultado de JSON: "Infinity"

Entrada de SQL: "-inf"
Resultado de JSON: "-Infinity"

Entrada de SQL: "NaN"
Resultado de JSON: "NaN"
STRING

string

Codificada como una string, con escape según el estándar JSON. En específico, se escapan ", \ y los caracteres de control de U+0000 a U+001F.

Entrada de SQL: "abc"
Resultado de JSON: "abc"

Entrada de SQL: "\"abc\""
Resultado de JSON: "\"abc\""
BYTES

string

Usa la codificación de datos Base64 RFC4648.

Entrada de SQL: b"Google"
Resultado de JSON: "R29vZ2xl"
DATE string Entrada de SQL: DATE '2017-03-06'
Resultado de JSON: "2017-03-06"
TIMESTAMP

string

Se codifica como fecha y hora ISO 8601, en el que T separa la fecha y la hora, y Z (Zulú/UTC) representa la zona horaria.

Entrada de SQL: TIMESTAMP '2017-03-06 12:34:56.789012'
Resultado de JSON: "2017-03-06T12:34:56.789012Z"
DATETIME

string

Se codifica como fecha y hora ISO 8601, en el que T separa la fecha y la hora.

Entrada de SQL: DATETIME '2017-03-06 12:34:56.789012'
Resultado de JSON: "2017-03-06T12:34:56.789012"
TIME

string

Codificada como hora ISO 8601.

Entrada de SQL: TIME '12:34:56.789012'
Resultado de JSON: "12:34:56.789012"
ARRAY

array

Puede contener cero o más elementos. Cada elemento tiene un formato según su tipo.

Entrada de SQL: ["red", "blue", "green"]
Resultado de JSON: ["red", "blue", "green"]

Entrada de SQL:[1, 2, 3]
Resultado de JSON:[1, 2, 3]
STRUCT

objeto

El objeto puede contener cero o más pares clave-valor. Cada valor tiene un formato según su tipo.

Para TO_JSON_STRING, un campo y cualquier duplicado de este campo se incluyen en la string de resultado.

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.

Entrada de SQL: STRUCT(12 AS purchases, TRUE AS inStock)
Resultado de JSON: {"purchases":12,"inStock": true}

JSONPath

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