Funciones JSON

Organízate con las colecciones Guarda y clasifica el contenido según tus preferencias.

SQL estándar de Google para BigQuery es compatible con las siguientes funciones, que pueden recuperar y transformar datos 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 o un valor booleano. STRING o JSON con formato JSON
JSON_VALUE Extrae un valor escalar. Un valor escalar puede representar una string, un número 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 y booleanos. ARRAY<JSON-formatted STRING> o ARRAY<JSON>
JSON_VALUE_ARRAY Extrae un array de valores escalares. Un valor escalar puede representar una string, un número 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 SQL estándar de Google, 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 o un valor booleano. STRING o JSON con formato JSON
JSON_EXTRACT_SCALAR Extrae un valor escalar. Un valor escalar puede representar una string, un número 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 y booleanos. ARRAY<JSON-formatted STRING> o ARRAY<JSON>
JSON_EXTRACT_STRING_ARRAY Extrae un array de valores escalares. Un valor escalar puede representar una string, un número 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
PARSE_JSON Toma una string con formato JSON y muestra un valor JSON. JSON
TO_JSON Toma un valor SQL y muestra un valor JSON. JSON
TO_JSON_STRING Toma un valor SQL y muestra una representación de string con formato JSON del valor. STRING con formato JSON
STRING Extrae una string de JSON. STRING
BOOL Extrae un valor booleano de JSON. BOOL
INT64 Extrae un número entero de 64 bits de JSON. INT64
FLOAT64 Extrae un número de punto flotante de 64 bits de JSON. FLOAT64
JSON_TYPE Muestra el tipo del valor JSON más externo como una string. STRING

JSON_EXTRACT

JSON_EXTRACT(json_string_expr, json_path)
JSON_EXTRACT(json_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 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"}]}}'
    

    Extrae un NULL de SQL cuando se encuentra una string null con formato JSON. Por ejemplo:

    SELECT JSON_EXTRACT("null", "$") -- Returns a SQL NULL
    
  • json_expr: JSON. Por ejemplo:

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

    Extrae un null de JSON cuando se encuentra un null de JSON.

    SELECT JSON_EXTRACT(JSON 'null', "$") -- Returns a JSON 'null'
    
  • json_path: El JSONPath. Esto identifica los datos que deseas obtener de la entrada.

Tipo de datos que se muestra

  • json_string_expr: Un STRING con formato JSON
  • json_expr: JSON

Ejemplos

En el siguiente ejemplo, se extraen los datos JSON y se muestran como JSON.

SELECT
  JSON_EXTRACT(JSON '{"class":{"students":[{"id":5},{"id":12}]}}', '$.class')
  AS json_data;

+-----------------------------------+
| json_data                         |
+-----------------------------------+
| {"students":[{"id":5},{"id":12}]} |
+-----------------------------------+

En los siguientes ejemplos, se extraen los datos JSON y se muestran como strings con formato JSON.

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('{"a":null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_EXTRACT('{"a":null}', "$.b"); -- Returns a SQL NULL
SELECT JSON_EXTRACT(JSON '{"a":null}', "$.a"); -- Returns a JSON 'null'
SELECT JSON_EXTRACT(JSON '{"a":null}', "$.b"); -- Returns a SQL NULL

JSON_QUERY

JSON_QUERY(json_string_expr, json_path)
JSON_QUERY(json_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 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"}]}}'
    

    Extrae un NULL de SQL cuando se encuentra una string null con formato JSON. Por ejemplo:

    SELECT JSON_QUERY("null", "$") -- Returns a SQL NULL
    
  • json_expr: JSON. Por ejemplo:

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

    Extrae un null de JSON cuando se encuentra un null de JSON.

    SELECT JSON_QUERY(JSON 'null', "$") -- Returns a JSON 'null'
    
  • json_path: El JSONPath. Esto identifica los datos que deseas obtener de la entrada.

Tipo de datos que se muestra

  • json_string_expr: Un STRING con formato JSON
  • json_expr: JSON

Ejemplos

En el siguiente ejemplo, se extraen los datos JSON y se muestran como JSON.

SELECT
  JSON_QUERY(JSON '{"class":{"students":[{"id":5},{"id":12}]}}', '$.class')
  AS json_data;

+-----------------------------------+
| json_data                         |
+-----------------------------------+
| {"students":[{"id":5},{"id":12}]} |
+-----------------------------------+

En los siguientes ejemplos, se extraen los datos JSON y se muestran como strings con formato JSON.

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('{"a":null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_QUERY('{"a":null}', "$.b"); -- Returns a SQL NULL
SELECT JSON_QUERY(JSON '{"a":null}', "$.a"); -- Returns a JSON 'null'
SELECT JSON_QUERY(JSON '{"a":null}', "$.b"); -- Returns a SQL NULL

JSON_EXTRACT_SCALAR

JSON_EXTRACT_SCALAR(json_string_expr[, json_path])
JSON_EXTRACT_SCALAR(json_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 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_expr: JSON. Por ejemplo:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: El JSONPath. Esto identifica los datos que deseas obtener de la entrada. Si no se proporciona este parámetro opcional, se aplica el símbolo $ de JSONPath, lo que significa que se analizan todos los datos.

    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, se muestra un SQL NULL.

Tipo de datos que se muestra

STRING

Ejemplos

En el siguiente ejemplo, se extrae age.

SELECT JSON_EXTRACT_SCALAR(JSON '{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+------------+
| scalar_age |
+------------+
| 6          |
+------------+

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])
JSON_VALUE(json_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 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_expr: JSON. Por ejemplo:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: El JSONPath. Esto identifica los datos que deseas obtener de la entrada. Si no se proporciona este parámetro opcional, se aplica el símbolo $ de JSONPath, lo que significa que se analizan todos los datos.

    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, se muestra un SQL NULL.

Tipo de datos que se muestra

STRING

Ejemplos

En el siguiente ejemplo, se extraen los datos JSON y se muestran como un valor escalar.

SELECT JSON_VALUE(JSON '{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+------------+
| scalar_age |
+------------+
| 6          |
+------------+

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

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])
JSON_EXTRACT_ARRAY(json_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 y valores 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_expr: JSON. Por ejemplo:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: El JSONPath. Esto identifica los datos que deseas obtener de la entrada. Si no se proporciona este parámetro opcional, se aplica el símbolo $ de JSONPath, lo que significa que se analizan todos los datos.

Tipo de datos que se muestra

  • json_string_expr: ARRAY<JSON-formatted STRING>
  • json_expr: ARRAY<JSON>

Ejemplos

En este ejemplo, se extraen elementos en JSON en un array de valores JSON:

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

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

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])
JSON_QUERY_ARRAY(json_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 y valores 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_expr: JSON. Por ejemplo:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: El JSONPath. Esto identifica los datos que deseas obtener de la entrada. Si no se proporciona este parámetro opcional, se aplica el símbolo $ de JSONPath, lo que significa que se analizan todos los datos.

Tipo de datos que se muestra

  • json_string_expr: ARRAY<JSON-formatted STRING>
  • json_expr: ARRAY<JSON>

Ejemplos

En este ejemplo, se extraen elementos en JSON en un array de valores JSON:

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

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

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_VALUE(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])
JSON_EXTRACT_STRING_ARRAY(json_expr[, json_path])

Descripción

Extrae un array de valores escalares y muestra un arreglo de valores escalares con formato de string. Un valor escalar puede representar una string, un número 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_expr: JSON. Por ejemplo:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: El JSONPath. Esto identifica los datos que deseas obtener de la entrada. Si no se proporciona este parámetro opcional, se aplica el símbolo $ de JSONPath, lo que significa que se analizan todos los datos.

Advertencias:

  • Un null de JSON en el array de entrada produce un NULL de SQL como el resultado de ese null de JSON. Si el resultado contiene un elemento de array NULL, se produce un error porque el resultado final no puede ser un array con valores NULL.
  • Si un JSONPath coincide con un array que contiene objetos escalares y un null de JSON, el resultado de la función debe transformarse porque el resultado final no puede ser un array con valores NULL.

Tipo de datos que se muestra

ARRAY<STRING>

Ejemplos

Aquí, se extraen elementos en JSON en un array de strings:

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

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

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

-- The following query produces and error because the final output cannot be an
-- array with NULLs.
SELECT JSON_EXTRACT_STRING_ARRAY('["world", 1, null]') AS result;

JSON_VALUE_ARRAY

JSON_VALUE_ARRAY(json_string_expr[, json_path])
JSON_VALUE_ARRAY(json_expr[, json_path])

Descripción

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

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

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON. Por ejemplo:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: El JSONPath. Esto identifica los datos que deseas obtener de la entrada. Si no se proporciona este parámetro opcional, se aplica el símbolo $ de JSONPath, lo que significa que se analizan todos los datos.

Advertencias:

  • Un null de JSON en el array de entrada produce un NULL de SQL como el resultado de un null de JSON. Si el resultado contiene un elemento de array NULL, se produce un error porque el resultado final no puede ser un array con valores NULL.
  • Si un JSONPath coincide con un array que contiene objetos escalares y un null de JSON, el resultado de la función debe transformarse porque el resultado final no puede ser un array con valores NULL.

Tipo de datos que se muestra

ARRAY<STRING>

Ejemplos

Aquí, se extraen elementos en JSON en un array de strings:

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

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

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

-- The following query produces and error because the final output cannot be an
-- array with NULLs.
SELECT JSON_VALUE_ARRAY('["world", 1, null]') AS result;

PARSE_JSON

PARSE_JSON(json_string_expr[, wide_number_mode=>{ 'exact' | 'round' }])

Descripción

Toma un valor STRING de SQL y muestra un valor JSON de SQL. El valor STRING representa un valor JSON con formato de string.

Esta función admite un argumento con nombre obligatorio opcional llamado wide_number_mode que determina cómo manejar los números que no se pueden almacenar en un valor JSON sin pérdida de precisión. Si se usa, wide_number_mode debe incluir uno de estos valores:

  • exact: Solo acepta números que se pueden almacenar sin pérdida de precisión. Si se encuentra un número que no se puede almacenar sin pérdida de precisión, la función muestra un error.
  • round: Si se encuentra un número que no se puede almacenar sin pérdida de precisión, intenta redondearlo a un número que se pueda almacenar sin pérdida de precisión. Si el número no se puede redondear, la función muestra un error.

Si wide_number_mode no se usa, la función incluye wide_number_mode=>'exact' de forma implícita. Si aparece un número en un objeto o arreglo JSON, el argumento wide_number_mode se aplica al número en el objeto o arreglo.

Los números de los siguientes dominios pueden almacenarse en JSON sin pérdida de precisión:

  • Números enteros con o sin firma de 64 bits, como INT64
  • FLOAT64

Tipo de datos que se muestra

JSON

Ejemplos

En el siguiente ejemplo, una string con formato JSON se convierte en JSON.

SELECT PARSE_JSON('{"coordinates":[10,20],"id":1}') AS json_data;

+--------------------------------+
| json_data                      |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
+--------------------------------+

Las siguientes consultas fallan debido a lo siguiente:

  • El número que se pasó no se puede almacenar sin perder precisión.
  • wide_number_mode=>'exact' se usa de forma implícita en la primera consulta y de forma explícita en la segunda consulta.
SELECT PARSE_JSON('{"id":922337203685477580701}') AS json_data; -- fails
SELECT PARSE_JSON('{"id":922337203685477580701}', wide_number_mode=>'exact') AS json_data; -- fails

La siguiente consulta redondea el número a un número que se puede almacenar en JSON.

SELECT PARSE_JSON('{"id":922337203685477580701}', wide_number_mode=>'round') AS json_data;

+--------------------------------+
| json_data                      |
+--------------------------------+
| {"id":9.223372036854776e+20}   |
+--------------------------------+

TO_JSON

TO_JSON(sql_value[, stringify_wide_numbers=>{ TRUE | FALSE }])

Descripción

Toma un valor SQL y muestra un valor JSON. El valor debe ser un tipo de datos compatible con SQL estándar de Google. Puedes revisar los tipos de datos de SQL estándar de Google que admite esta función y sus codificaciones JSON aquí.

Esta función admite un argumento con nombre obligatorio opcional llamado stringify_wide_numbers.

  • Si este argumento es TRUE, los valores numéricos fuera del dominio de tipo FLOAT64 se codifican como strings.
  • Si este argumento no se usa o es FALSE, los valores numéricos fuera del dominio de tipo FLOAT64 no se codifican como strings, pero se almacenan como números JSON. Se muestra un error si un valor numérico no se puede almacenar en JSON sin pérdida de precisión.

Los argumentos de stringify_wide_numbers afectan los siguientes tipos de datos numéricos:

  • INT64
  • NUMERIC
  • BIGNUMERIC

Si uno de estos tipos de datos numéricos aparece en un tipo de datos de contenedor, como ARRAY o STRUCT, el argumento stringify_wide_numbers se aplica a los tipos de datos numéricos en el tipo de datos del contenedor.

Tipo de datos que se muestra

Un valor JSON

Ejemplos

En el siguiente ejemplo, la consulta convierte las filas de una tabla en valores 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 TO_JSON(t) AS json_objects
FROM CoordinatesTable AS t;

+--------------------------------+
| json_objects                   |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
| {"coordinates":[30,40],"id":2} |
| {"coordinates":[50,60],"id":3} |
+--------------------------------+

En el siguiente ejemplo, la consulta muestra un valor numérico grande como una string JSON.

SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>TRUE) as stringify_on

+--------------------+
| stringify_on       |
+--------------------+
| "9007199254740993" |
+--------------------+

En el siguiente ejemplo, ambas consultas muestran un valor numérico grande como número JSON.

SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>FALSE) as stringify_off
SELECT TO_JSON(9007199254740993) as stringify_off

+------------------+
| stringify_off    |
+------------------+
| 9007199254740993 |
+------------------+

En el siguiente ejemplo, solo los valores numéricos grandes se convierten en strings JSON.

With T1 AS (
  (SELECT 9007199254740993 AS id) UNION ALL
  (SELECT 2 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;

+---------------------------+
| json_objects              |
+---------------------------+
| {"id":"9007199254740993"} |
| {"id":2}                  |
+---------------------------+

En este ejemplo, los valores 9007199254740993 (INT64) y 2.1 (FLOAT64) se convierten en el supertipo común FLOAT64, que no es afectado por el argumento stringify_wide_numbers.

With T1 AS (
  (SELECT 9007199254740993 AS id) UNION ALL
  (SELECT 2.1 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;

+------------------------------+
| json_objects                 |
+------------------------------+
| {"id":9.007199254740992e+15} |
| {"id":2.1}                   |
+------------------------------+

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 SQL estándar de Google. Puedes revisar los tipos de datos de SQL estándar de Google 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             |
|    |             |   ]                |
|    |             | }                  |
+----+-------------+--------------------+

STRING

STRING(json_expr)

Descripción

Toma una expresión JSON, extrae una string JSON y muestra ese valor como una STRING de SQL. Si la expresión es NULL de SQL, la función muestra NULL de SQL. Se producirá un error si el valor JSON extraído no es una string.

  • json_expr: JSON. Por ejemplo:

    JSON '{"name": "sky", "color" : "blue"}'
    

Tipo de datos que se muestra

STRING

Ejemplos

SELECT STRING(JSON '"purple"') AS color;

+--------+
| color  |
+--------+
| purple |
+--------+
SELECT STRING(JSON_QUERY(JSON '{"name": "sky", "color": "blue"}', "$.color")) AS color;

+-------+
| color |
+-------+
| blue  |
+-------+

En los siguientes ejemplos, se muestra cómo se manejan las solicitudes no válidas:

-- An error is thrown if the JSON is not of type string.
SELECT STRING(JSON '123') AS result; -- Throws an error
SELECT STRING(JSON 'null') AS result; -- Throws an error
SELECT SAFE.STRING(JSON '123') AS result; -- Returns a SQL NULL

BOOL

BOOL(json_expr)

Descripción

Toma una expresión JSON, extrae una string JSON y muestra ese valor como una BOOL de SQL. Si la expresión es NULL de SQL, la función muestra NULL de SQL. Se producirá un error si el valor JSON extraído no es una string.

  • json_expr: JSON. Por ejemplo:

    JSON '{"name": "sky", "color" : "blue"}'
    

Tipo de datos que se muestra

BOOL

Ejemplos

SELECT BOOL(JSON 'true') AS vacancy;

+---------+
| vacancy |
+---------+
| true    |
+---------+
SELECT BOOL(JSON_QUERY(JSON '{"hotel class": "5-star", "vacancy": true}', "$.vacancy")) AS vacancy;

+---------+
| vacancy |
+---------+
| true    |
+---------+

En los siguientes ejemplos, se muestra cómo se manejan las solicitudes no válidas:

-- An error is thrown if JSON is not of type bool.
SELECT BOOL(JSON '123') AS result; -- Throws an error
SELECT BOOL(JSON 'null') AS result; -- Throw an error
SELECT SAFE.BOOL(JSON '123') AS result; -- Returns a SQL NULL

INT64

INT64(json_expr)

Descripción

Toma una expresión JSON, extrae un número JSON y muestra ese valor como un INT64 de SQL. Si la expresión es NULL de SQL, la función muestra NULL de SQL. Se producirá un error si el número JSON extraído tiene una parte fraccionaria o está fuera del dominio INT64.

  • json_expr: JSON. Por ejemplo:

    JSON '{"name": "sky", "color" : "blue"}'
    

Tipo de datos que se muestra

INT64

Ejemplos

SELECT INT64(JSON '2005') AS flight_number;

+---------------+
| flight_number |
+---------------+
| 2005          |
+---------------+
SELECT INT64(JSON_QUERY(JSON '{"gate": "A4", "flight_number": 2005}', "$.flight_number")) AS flight_number;

+---------------+
| flight_number |
+---------------+
| 2005          |
+---------------+
SELECT INT64(JSON '10.0') AS score;

+-------+
| score |
+-------+
| 10    |
+-------+

En los siguientes ejemplos, se muestra cómo se manejan las solicitudes no válidas:

-- An error is thrown if JSON is not a number or cannot be converted to a 64-bit integer.
SELECT INT64(JSON '10.1') AS result;  -- Throws an error
SELECT INT64(JSON '"strawberry"') AS result; -- Throws an error
SELECT INT64(JSON 'null') AS result; -- Throws an error
SELECT SAFE.INT64(JSON '"strawberry"') AS result;  -- Returns a SQL NULL

FLOAT64

FLOAT64(json_expr[, wide_number_mode=>{ 'exact' | 'round' }])

Descripción

Toma una expresión JSON, extrae un número JSON y muestra ese valor como un FLOAT64 de SQL. Si la expresión es NULL de SQL, la función muestra NULL de SQL. Se producirá un error si el valor JSON extraído no es un número.

  • json_expr: JSON. Por ejemplo:

    JSON '{"name": "sky", "color" : "blue"}'
    

Esta función admite un argumento con nombre obligatorio opcional llamado wide_number_mode que define lo que sucede con un número que no se puede representar como FLOAT64 sin pérdida de precisión.

En este argumento, se acepta uno de los dos valores que distinguen mayúsculas de minúsculas:

  • “exact”: La función falla si no se puede representar el resultado como un FLOAT64 sin pérdida de precisión.
  • “round”: El valor numérico almacenado en JSON se redondeará a FLOAT64. Si no es posible redondearlo, la función falla. Este es el valor predeterminado si no se especifica el argumento.

Tipo de datos que se muestra

FLOAT64

Ejemplos

SELECT FLOAT64(JSON '9.8') AS velocity;

+----------+
| velocity |
+----------+
| 9.8      |
+----------+
SELECT FLOAT64(JSON_QUERY(JSON '{"vo2_max": 39.1, "age": 18}', "$.vo2_max")) AS vo2_max;

+---------+
| vo2_max |
+---------+
| 39.1    |
+---------+
SELECT FLOAT64(JSON '18446744073709551615', wide_number_mode=>'round') as result;

+------------------------+
| result                 |
+------------------------+
| 1.8446744073709552e+19 |
+------------------------+
SELECT FLOAT64(JSON '18446744073709551615') as result;

+------------------------+
| result                 |
+------------------------+
| 1.8446744073709552e+19 |
+------------------------+

En los siguientes ejemplos, se muestra cómo se manejan las solicitudes no válidas:

-- An error is thrown if JSON is not of type FLOAT64.
SELECT FLOAT64(JSON '"strawberry"') AS result;
SELECT FLOAT64(JSON 'null') AS result;

-- An error is thrown because `wide_number_mode` is case-sensitive and not "exact" or "round".
SELECT FLOAT64(JSON '123.4', wide_number_mode=>'EXACT') as result;
SELECT FLOAT64(JSON '123.4', wide_number_mode=>'exac') as result;

-- An error is thrown because the number cannot be converted to DOUBLE without loss of precision
SELECT FLOAT64(JSON '18446744073709551615', wide_number_mode=>'exact') as result;

-- Returns a SQL NULL
SELECT SAFE.FLOAT64(JSON '"strawberry"') AS result;

JSON_TYPE

JSON_TYPE(json_expr)

Descripción

Toma una expresión JSON y muestra el tipo del valor JSON más externo como una STRING de SQL. Se pueden mostrar los nombres de estos tipos de JSON:

  • object
  • array
  • string
  • number
  • boolean
  • null

Si la expresión es NULL de SQL, la función muestra NULL de SQL. Se producirá un error si el valor JSON extraído no es un tipo JSON válido.

  • json_expr: JSON. Por ejemplo:

    JSON '{"name": "sky", "color" : "blue"}'
    

Tipo de datos que se muestra

STRING

Ejemplos

SELECT json_val, JSON_TYPE(json_val) AS type
FROM
  UNNEST(
    [
      JSON '"apple"',
      JSON '10',
      JSON '3.14',
      JSON 'null',
      JSON '{"city": "New York", "State": "NY"}',
      JSON '["apple", "banana"]',
      JSON 'false'
    ]
  ) AS json_val;

+----------------------------------+---------+
| json_val                         | type    |
+----------------------------------+---------+
| "apple"                          | string  |
| 10                               | number  |
| 3.14                             | number  |
| null                             | null    |
| {"State":"NY","city":"New York"} | object  |
| ["apple","banana"]               | array   |
| false                            | boolean |
+----------------------------------+---------+

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 o TO_JSON.

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

(solo TO_JSON_STRING)

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"
INT64

(solo TO_JSON)

número o string

Si el argumento stringify_wide_numbers es TRUE y el valor está fuera del dominio de tipo FLOAT64, el valor se codifica como una string. Si el valor no se puede almacenar en JSON sin pérdida de precisión, la función falla. De lo contrario, el valor se codifica como un número.

Si stringify_wide_numbers no se usa o es FALSE, los valores numéricos fuera del dominio de tipo “FLOAT64” no se codifican como strings, sino que se almacenan como números JSON. Se muestra un error si un valor numérico no se puede almacenar en JSON sin pérdida de precisión.

Entrada de SQL: 9007199254740992
Salida de JSON: 9007199254740992

Entrada de SQL: 9007199254740993
Salida de JSON: 9007199254740993

Entrada de SQL con stringify_wide_numbers=>TRUE: 9007199254740992
Salida de JSON: 9007199254740992

Entrada de SQL con stringify_wide_numbers=>TRUE: 9007199254740993
Salida de JSON: "9007199254740993"
NUMERIC
BIGNUMERIC

(solo TO_JSON_STRING)

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"
NUMERIC
BIGNUMERIC

(solo TO_JSON)

número o string

Si el argumento stringify_wide_numbers es TRUE y el valor está fuera del dominio de tipo FLOAT64, se codifica como una string. De lo contrario, se codifica como un número.

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

Entrada de SQL: 0
Salida de JSON: 0

Entrada de SQL: 9007199254740993
Salida de JSON: 9007199254740993

Entrada de SQL: 123.56
Salida de JSON: 123.56

Entrada de SQL con stringify_wide_numbers=>TRUE: 9007199254740993
Salida de JSON: "9007199254740993"

Entrada de SQL con stringify_wide_numbers=>TRUE: 123.56
Salida 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 un número.

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"
JSON

los datos del JSON de entrada

Entrada de SQL: JSON '{"item": "pen", "price": 10}'
Salida de JSON: {"item":"pen", "price":10}

Entrada de SQL:[1, 2, 3]
Salida de JSON:[1, 2, 3]
ARRAY

array

Puede contener cero o más elementos.

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

Entrada de SQL:[1, 2, 3]
Salida 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, se incluye un campo en la string de salida y se omiten todos los duplicados de este campo. Para TO_JSON_STRING, se incluye un campo y cualquier duplicado de este campo en la string de salida.

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.

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

Formato JSONPath

Con el formato JSONPath, puedes identificar los valores que deseas obtener de una string con formato JSON. El formato JSONPath admite estos operadores:

Operador Descripción Ejemplos
$ Objeto o elemento raíz. El formato JSONPath debe comenzar con este operador, que hace referencia al nivel más externo de la string con formato JSON.

String con formato JSON:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

Ruta de acceso JSON:
"$"

Resultado de JSON:
{"class":{"students":[{"name":"Jane"}]}}

. Operador secundario. Puedes identificar los valores secundarios con la notación de puntos.

String con formato JSON:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

Ruta de acceso JSON:
"$.class.students"

Resultado de JSON:
[{"name":"Jane"}]

[] Operador subíndice. Si el objeto JSON es un array, puedes usar corchetes para especificar el índice del array.

String con formato JSON:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

Ruta de acceso JSON:
"$.class.students[0]"

Resultado de JSON:
{"name":"Jane"}

Si una clave en una función JSON contiene un operador de formato JSON, consulta cada función JSON para saber cómo marcarlas.

Una función JSON muestra NULL si el formato JSONPath no coincide con un valor en una string con formato JSON. Si el valor seleccionado para una función escalar no es escalar, como un objeto o un array, la función muestra NULL. Se producirá un error si el formato JSONPath no es válido.