Trabajar con datos JSON en GoogleSQL

En este documento se describe cómo crear una tabla con una columna JSON, insertar datos JSON en una tabla de BigQuery y consultar datos JSON.

BigQuery admite de forma nativa datos JSON mediante el tipo de datos JSON.

JSON es un formato muy utilizado que permite usar datos semiestructurados, ya que no requiere un esquema. Las aplicaciones pueden usar un enfoque de "esquema al leer", en el que la aplicación ingiere los datos y, a continuación, realiza consultas basadas en suposiciones sobre el esquema de esos datos. Este enfoque difiere del tipo STRUCT de BigQuery, que requiere un esquema fijo que se aplica a todos los valores almacenados en una columna de tipo STRUCT.

Si usas el tipo de datos JSON, puedes cargar JSON semiestructurado en BigQuery sin proporcionar un esquema para los datos JSON por adelantado. Esto te permite almacenar y consultar datos que no siempre se ajustan a esquemas y tipos de datos fijos. Al ingerir datos JSON como un tipo de datos JSON, BigQuery puede codificar y procesar cada campo JSON de forma individual. A continuación, puedes consultar los valores de los campos y los elementos de la matriz de los datos JSON mediante el operador de acceso a campos, lo que hace que las consultas JSON sean intuitivas y rentables.

Limitaciones

  • Si usas una tarea de carga por lotes para insertar datos JSON en una tabla, los datos de origen deben estar en formato CSV, Avro o JSON. No se admiten otros formatos de carga por lotes.
  • El tipo de datos JSON tiene un límite de anidación de 500.
  • No puedes usar SQL antiguo para consultar una tabla que contenga tipos JSON.
  • Las políticas de acceso a nivel de fila no se pueden aplicar a las columnas JSON.

Para obtener información sobre las propiedades del tipo de datos JSON, consulta el tipo JSON.

Crear una tabla con una columna JSON

Puedes crear una tabla vacía con una columna JSON mediante SQL o la herramienta de línea de comandos bq.

SQL

Usa la instrucción CREATE TABLE y declara una columna con el tipo JSON.

  1. En la Google Cloud consola, ve a la página BigQuery.

    Ir a BigQuery

  2. En el editor de consultas, introduce la siguiente instrucción:

    CREATE TABLE mydataset.table1(
  id INT64,
  cart JSON
);

  3. Haz clic en Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, consulta Ejecutar una consulta interactiva.

bq

Usa el comando bq mk y proporciona un esquema de tabla con un tipo de datos JSON.

bq mk --table mydataset.table1 id:INT64,cart:JSON

No puedes particionar ni agrupar una tabla en columnas JSON, porque los operadores de igualdad y comparación no están definidos en el tipo JSON.

Crear valores JSON

Puedes crear valores JSON de las siguientes formas:

  • Usa SQL para crear un JSON literal.
  • Usa la función PARSE_JSON para convertir un valor STRING en un valor JSON.
  • Usa la función TO_JSON para convertir un valor de SQL en un valor de JSON.
  • Usa la función JSON_ARRAY para crear una matriz JSON a partir de valores SQL.
  • Usa la función JSON_OBJECT para crear un objeto JSON a partir de pares clave-valor.

Crear un valor de JSON

En el siguiente ejemplo se insertan valores de JSON en una tabla:

INSERT INTO mydataset.table1 VALUES
(1, JSON '{"name": "Alice", "age": 30}'),
(2, JSON_ARRAY(10, ['foo', 'bar'], [20, 30])),
(3, JSON_OBJECT('foo', 10, 'bar', ['a', 'b']));

Convertir un tipo STRING en un tipo JSON

En el siguiente ejemplo se convierte un valor STRING con formato JSON mediante la función PARSE_JSON. En este ejemplo, se convierte una columna de una tabla en un tipo JSON y se guardan los resultados en una tabla nueva.

CREATE OR REPLACE TABLE mydataset.table_new
AS (
  SELECT
    id, SAFE.PARSE_JSON(cart) AS cart_json
  FROM
    mydataset.old_table
);

El prefijo SAFE usado en este ejemplo asegura que los errores de conversión se devuelvan como valores NULL.

Convertir datos esquematizados en JSON

En el siguiente ejemplo se convierten pares clave-valor en JSON mediante la función JSON_OBJECT.

WITH Fruits AS (
SELECT 0 AS id, 'color' AS k, 'Red' AS v UNION ALL
SELECT 0, 'fruit', 'apple' UNION ALL
SELECT 1, 'fruit','banana' UNION ALL
SELECT 1, 'ripe', 'true'
)

SELECT JSON_OBJECT(ARRAY_AGG(k), ARRAY_AGG(v)) AS json_data
FROM Fruits
GROUP BY id

El resultado es el siguiente:

+----------------------------------+
| json_data                        |
+----------------------------------+
| {"color":"Red","fruit":"apple"}  |
| {"fruit":"banana","ripe":"true"} |
+----------------------------------+

Convertir un tipo de SQL en un tipo JSON

En el siguiente ejemplo, se convierte un valor STRUCT de SQL en un valor JSON mediante la función TO_JSON:

SELECT TO_JSON(STRUCT(1 AS id, [10,20] AS coordinates)) AS pt;

El resultado es el siguiente:

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

Ingerir datos JSON

Puedes ingerir datos JSON en una tabla de BigQuery de las siguientes formas:

Cargar desde archivos CSV

En el siguiente ejemplo se da por supuesto que tiene un archivo CSV llamado file1.csv que contiene los siguientes registros:

1,20
2,"""This is a string"""
3,"{""id"": 10, ""name"": ""Alice""}"

Ten en cuenta que la segunda columna contiene datos JSON codificados como una cadena. Esto implica escapar correctamente las comillas para el formato CSV. En formato CSV, las comillas se escapan con la secuencia de dos caracteres "".

Para cargar este archivo con la herramienta de línea de comandos bq, usa el comando bq load:

bq load --source_format=CSV mydataset.table1 file1.csv id:INTEGER,json_data:JSON

bq show mydataset.table1

Last modified          Schema         Total Rows   Total Bytes
----------------- -------------------- ------------ -------------
 22 Dec 22:10:32   |- id: integer       3            63
                   |- json_data: json

Cargar datos de archivos JSON delimitados por líneas nuevas

En el siguiente ejemplo se da por hecho que tienes un archivo llamado file1.jsonl que contiene los siguientes registros:

{"id": 1, "json_data": 20}
{"id": 2, "json_data": "This is a string"}
{"id": 3, "json_data": {"id": 10, "name": "Alice"}}

Para cargar este archivo con la herramienta de línea de comandos bq, usa el comando bq load:

bq load --source_format=NEWLINE_DELIMITED_JSON mydataset.table1 file1.jsonl id:INTEGER,json_data:JSON

bq show mydataset.table1

Last modified          Schema         Total Rows   Total Bytes
----------------- -------------------- ------------ -------------
 22 Dec 22:10:32   |- id: integer       3            63
                   |- json_data: json

Usar la API Storage Write

Puedes usar la API Storage Write para ingerir datos JSON. En el siguiente ejemplo se usa el cliente Python de la API Storage Write para escribir datos en una tabla con una columna de tipo de datos JSON.

Define un búfer de protocolo para contener los datos de streaming serializados. Los datos JSON se codifican como una cadena. En el siguiente ejemplo, el campo json_col contiene datos JSON.

message SampleData {
  optional string string_col = 1;
  optional int64 int64_col = 2;
  optional string json_col = 3;
}

Aplica el formato de los datos JSON de cada fila como un valor STRING:

row.json_col = '{"a": 10, "b": "bar"}'
row.json_col = '"This is a string"' # The double-quoted string is the JSON value.
row.json_col = '10'

Añade las filas al flujo de escritura tal como se muestra en el código de ejemplo. La biblioteca de cliente se encarga de la serialización al formato de búfer de protocolo.

Si no puede dar formato a los datos JSON entrantes, debe usar el método json.dumps() en su código. A continuación se muestra un ejemplo:

import json

...

row.json_col = json.dumps({"a": 10, "b": "bar"})
row.json_col = json.dumps("This is a string") # The double-quoted string is the JSON value.
row.json_col = json.dumps(10)

...

Usar la API de streaming antigua

En el siguiente ejemplo se cargan datos JSON de un archivo local y se transmiten a una tabla de BigQuery con una columna de tipo de datos JSON llamada json_data mediante la API de transmisión antigua.

from google.cloud import bigquery
import json

# TODO(developer): Replace these variables before running the sample.
project_id = 'MY_PROJECT_ID'
table_id = 'MY_TABLE_ID'

client = bigquery.Client(project=project_id)
table_obj = client.get_table(table_id)

# The column json_data is represented as a JSON data-type column.
rows_to_insert = [
    {"id": 1, "json_data": 20},
    {"id": 2, "json_data": "This is a string"},
    {"id": 3, "json_data": {"id": 10, "name": "Alice"}}
]

# If the column json_data is represented as a String data type, modify the rows_to_insert values:
#rows_to_insert = [
#    {"id": 1, "json_data": json.dumps(20)},
#    {"id": 2, "json_data": json.dumps("This is a string")},
#    {"id": 3, "json_data": json.dumps({"id": 10, "name": "Alice"})}
#]

# Throw errors if encountered.
# https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.client.Client#google_cloud_bigquery_client_Client_insert_rows

errors = client.insert_rows(table=table_obj, rows=rows_to_insert)
if errors == []:
    print("New rows have been added.")
else:
    print("Encountered errors while inserting rows: {}".format(errors))

Para obtener más información, consulta el artículo sobre cómo transmitir datos a BigQuery.

Consultar datos JSON

En esta sección se describe cómo usar GoogleSQL para extraer valores de JSON. En JSON se distingue entre mayúsculas y minúsculas, y se admite UTF-8 tanto en los campos como en los valores.

En los ejemplos de esta sección se usa la siguiente tabla:

CREATE OR REPLACE TABLE mydataset.table1(id INT64, cart JSON);

INSERT INTO mydataset.table1 VALUES
(1, JSON """{
        "name": "Alice",
        "items": [
            {"product": "book", "price": 10},
            {"product": "food", "price": 5}
        ]
    }"""),
(2, JSON """{
        "name": "Bob",
        "items": [
            {"product": "pen", "price": 20}
        ]
    }""");

Extraer valores como JSON

Dado un tipo JSON en BigQuery, puedes acceder a los campos de una expresión JSON mediante el operador de acceso a campos. En el siguiente ejemplo se devuelve el campo name de la columna cart.

SELECT cart.name
FROM mydataset.table1;
 
+---------+
|  name   |
+---------+
| "Alice" |
| "Bob"   |
+---------+

Para acceder a un elemento de una matriz, usa el operador de subíndice JSON. En el siguiente ejemplo se devuelve el primer elemento del array items:

SELECT
  cart.items[0] AS first_item
FROM mydataset.table1
 
+-------------------------------+
|          first_item           |
+-------------------------------+
| {"price":10,"product":"book"} |
| {"price":20,"product":"pen"}  |
+-------------------------------+

También puedes usar el operador de subíndice JSON para hacer referencia a los miembros de un objeto JSON por su nombre:

SELECT cart['name']
FROM mydataset.table1;
 
+---------+
|  name   |
+---------+
| "Alice" |
| "Bob"   |
+---------+

En las operaciones de subíndice, la expresión entre corchetes puede ser cualquier cadena o expresión entera arbitraria, incluidas las expresiones no constantes:

DECLARE int_val INT64 DEFAULT 0;

SELECT
  cart[CONCAT('it','ems')][int_val + 1].product AS item
FROM mydataset.table1;
 
+--------+
|  item  |
+--------+
| "food" |
| NULL   |
+--------+

Los operadores de acceso a campos y de subíndice devuelven tipos JSON, por lo que puedes encadenar expresiones que los usen o pasar el resultado a otras funciones que acepten tipos JSON.

Estos operadores mejoran la legibilidad de la funcionalidad básica de la función JSON_QUERY. Por ejemplo, la expresión cart.name es equivalente a JSON_QUERY(cart, "$.name").

Si no se encuentra un miembro con el nombre especificado en el objeto JSON o si la matriz JSON no tiene un elemento con la posición especificada, estos operadores devuelven el valor SQL NULL.

SELECT
  cart.address AS address,
  cart.items[1].price AS item1_price
FROM
  mydataset.table1;
 
+---------+-------------+
| address | item1_price |
+---------+-------------+
| NULL    | NULL        |
| NULL    | 5           |
+---------+-------------+

Los operadores de igualdad y comparación no están definidos en el tipo de datos JSON. Por lo tanto, no puede usar valores de JSON directamente en cláusulas como GROUP BY o ORDER BY. En su lugar, usa la función JSON_VALUE para extraer valores de campo como cadenas SQL, tal como se describe en la siguiente sección.

Extraer valores como cadenas

La función JSON_VALUE extrae un valor escalar y lo devuelve como una cadena de SQL. Devuelve SQL NULL si cart.name no apunta a un valor escalar en el JSON.

SELECT JSON_VALUE(cart.name) AS name
FROM mydataset.table1;
 
+-------+
| name  |
+-------+
| Alice |
+-------+

Puedes usar la función JSON_VALUE en contextos que requieran igualdad o comparación, como las cláusulas WHERE y GROUP BY. En el siguiente ejemplo se muestra una cláusula WHERE que filtra por un valor JSON:

SELECT
  cart.items[0] AS first_item
FROM
  mydataset.table1
WHERE
  JSON_VALUE(cart.name) = 'Alice';
 
+-------------------------------+
| first_item                    |
+-------------------------------+
| {"price":10,"product":"book"} |
+-------------------------------+

También puedes usar la función STRING que extrae una cadena JSON y devuelve ese valor como STRING de SQL. Por ejemplo:

SELECT STRING(JSON '"purple"') AS color;
 
+--------+
| color  |
+--------+
| purple |
+--------+

Además de STRING, es posible que tengas que extraer valores de JSON y devolverlos como otro tipo de datos SQL. Están disponibles las siguientes funciones de extracción de valores:

Para obtener el tipo del valor JSON, puedes usar la función JSON_TYPE.

Convertir JSON de forma flexible

Puedes convertir un valor de JSON en un valor escalar de SQL de forma flexible con las funciones LAX conversion.

En el siguiente ejemplo se usa la función LAX_INT64 para extraer un valor INT64 de un valor JSON. 

SELECT LAX_INT64(JSON '"10"') AS id;
 
+----+
| id |
+----+
| 10 |
+----+

Además de LAX_INT64, puedes convertir otros tipos de SQL a JSON de forma flexible con las siguientes funciones:

Extraer matrices de JSON

JSON puede contener matrices JSON, que no son directamente equivalentes a un tipo ARRAY<JSON> en BigQuery. Puede usar las siguientes funciones para extraer un ARRAY de BigQuery de JSON:

  • JSON_QUERY_ARRAY: extrae una matriz y la devuelve como un ARRAY<JSON> de JSON.
  • JSON_VALUE_ARRAY: extrae un array de valores escalares y lo devuelve como un ARRAY<STRING> de valores escalares.

En el siguiente ejemplo se usa JSON_QUERY_ARRAY para extraer matrices JSON:

SELECT JSON_QUERY_ARRAY(cart.items) AS items
FROM mydataset.table1;
 
+----------------------------------------------------------------+
| items                                                          |
+----------------------------------------------------------------+
| [{"price":10,"product":"book"}","{"price":5,"product":"food"}] |
| [{"price":20,"product":"pen"}]                                 |
+----------------------------------------------------------------+

Para dividir una matriz en sus elementos individuales, use el operador UNNEST, que devuelve una tabla con una fila por cada elemento de la matriz. En el siguiente ejemplo se selecciona el miembro product de cada miembro de la matriz items:

SELECT
  id,
  JSON_VALUE(item.product) AS product
FROM
  mydataset.table1, UNNEST(JSON_QUERY_ARRAY(cart.items)) AS item
ORDER BY id;
 
+----+---------+
| id | product |
+----+---------+
|  1 | book    |
|  1 | food    |
|  2 | pen     |
+----+---------+

El siguiente ejemplo es similar, pero usa la función ARRAY_AGG para agregar los valores en una matriz SQL.

SELECT
  id,
  ARRAY_AGG(JSON_VALUE(item.product)) AS products
FROM
  mydataset.table1, UNNEST(JSON_QUERY_ARRAY(cart.items)) AS item
GROUP BY id
ORDER BY id;
 
+----+-----------------+
| id | products        |
+----+-----------------+
|  1 | ["book","food"] |
|  2 | ["pen"]         |
+----+-----------------+

Para obtener más información sobre las matrices, consulta Trabajar con matrices en GoogleSQL.

Valores nulos de JSON

El tipo JSON tiene un valor null especial que es diferente del SQL NULL. Un null JSON no se trata como un valor NULL de SQL, como se muestra en el siguiente ejemplo.

SELECT JSON 'null' IS NULL;
 
+-------+
| f0_   |
+-------+
| false |
+-------+

Cuando extraes un campo JSON con un valor null, el comportamiento depende de la función:

  • La función JSON_QUERY devuelve un null JSON, ya que es un valor JSON válido.
  • La función JSON_VALUE devuelve el valor NULL de SQL, ya que null de JSON no es un valor escalar.

En el siguiente ejemplo se muestran los diferentes comportamientos:

SELECT
  json.a AS json_query, -- Equivalent to JSON_QUERY(json, '$.a')
  JSON_VALUE(json, '$.a') AS json_value
FROM (SELECT JSON '{"a": null}' AS json);
 
+------------+------------+
| json_query | json_value |
+------------+------------+
| null       | NULL       |
+------------+------------+