Trabaja 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 es compatible de forma nativa con los datos JSON mediante el tipo de datos JSON.

JSON es un formato muy usado que permite datos semiestructurados, ya que no necesita un esquema. Las aplicaciones pueden usar un enfoque de “esquema en lectura”, en el que la aplicación transfiere los datos y, luego, realiza consultas a partir de suposiciones sobre el esquema de esos datos. Este enfoque difiere del tipo STRUCT en BigQuery, que necesita un esquema fijo que se aplica a todos los valores almacenados en una columna de tipo STRUCT.

Cuando usas el tipo de datos JSON, puedes cargar JSON semiestructurado a BigQuery sin proporcionar un esquema para los datos JSON por adelantado. Esto te permite almacenar y consultar datos que no siempre cumplen con los tipos de datos y esquemas fijos. Si se transfieren datos JSON como un tipo de datos JSON, BigQuery puede codificar y procesar cada campo JSON de forma individual. Luego, puedes consultar los valores de los campos y los elementos del array dentro de los datos JSON con el operador de acceso a campos, lo que hace que las consultas JSON sean intuitivas y rentables.

Limitaciones

  • Si usas un trabajo de carga por lotes para transferir datos JSON a 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 heredado para consultar una tabla que contiene 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 Tipo JSON.

Creación de una tabla con una columna JSON

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

SQL

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

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

    Ir a BigQuery

  2. En el editor de consultas, escribe la siguiente sentencia:

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

  3. Haz clic en Ejecutar.

Si deseas obtener información sobre cómo ejecutar consultas, visita Ejecuta 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 en clústeres una tabla en columnas JSON, ya que los operadores de igualdad y comparación no están definidos en el tipo JSON.

Crea valores JSON

Puedes crear valores JSON de las siguientes maneras:

  • Usa SQL para crear un literal JSON.
  • 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 SQL en un valor JSON.
  • Usa la función JSON_ARRAY para crear un array JSON a partir de valores SQL.
  • Usa la función JSON_OBJECT para crear un objeto JSON a partir de pares clave-valor.

Crea un valor JSON

En el siguiente ejemplo, se insertan valores 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']));

Convierte un tipo STRING a un tipo JSON

En el siguiente ejemplo, se convierte un valor STRING con formato JSON usando la función PARSE_JSON. En el ejemplo, una columna de una tabla existente se convierte a un tipo JSON y los resultados se almacenan 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 que se usa en este ejemplo garantiza que los errores de conversión se muestren como valores NULL.

Convierte datos esquematizados a JSON

En el siguiente ejemplo, se convierten pares clave-valor a JSON con 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"} |
+----------------------------------+

Convierte un tipo SQL a un tipo JSON

En el siguiente ejemplo, se convierte un valor STRUCT de SQL a un valor JSON con 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} |
+--------------------------------+

Transfiere datos JSON

Puedes transferir datos JSON a una tabla de BigQuery de las siguientes maneras:

Carga desde archivos CSV

En el siguiente ejemplo, se supone que tienes 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 de manera correcta las comillas para el formato CSV. En el formato CSV, las comillas se escapan mediante la secuencia de dos caracteres "".

Para cargar este archivo con la herramienta de línea de comandos de 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

Carga desde archivos JSON delimitados por saltos de línea

En el siguiente ejemplo, se supone 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 de 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

Usa la API de Storage Write

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

Define un búfer de protocolo para conservar los datos de transmisión 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;
}

Formatea 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'

Agrega las filas a la transmisión de escritura, como se muestra en el ejemplo de código. La biblioteca cliente controla la serialización al formato de búfer de protocolo.

Si no puedes dar formato a los datos JSON entrantes, debes usar el método json.dumps() en tu 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)

...

Usa la API de transmisión heredada

En el siguiente ejemplo, se cargan datos JSON desde 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 heredada.

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 Transmite datos a BigQuery.

Consulta datos JSON

En esta sección, se describe cómo usar GoogleSQL para extraer valores desde JSON. JSON distingue mayúsculas de minúsculas y es compatible con UTF-8 en campos y 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}
        ]
    }""");

Extrae valores como JSON

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

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

Para acceder a un elemento de arreglo, usa el operador de subíndice de JSON. En el siguiente ejemplo, se muestra el primer elemento del arreglo 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 de JSON para hacer referencia a los miembros de un objeto JSON por su nombre:

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

Para las operaciones de subíndice, la expresión dentro de los corchetes puede ser cualquier expresión de número entero o cadena 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 subíndice y acceso a campo muestran tipos JSON, por lo que puedes encadenar expresiones que las usen o pasar el resultado a otras funciones que reciban tipos JSON.

Estos operadores usan azúcar sintáctico para 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 el array JSON no tiene un elemento con la posición especificada, estos operadores muestran NULL SQL.

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 se definen en el tipo de datos JSON. Por lo tanto, no puedes usar valores 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, como se describe en la siguiente sección.

Extrae valores como strings

La función JSON_VALUE extrae un valor escalar y lo muestra como una string SQL. Muestra NULL SQL 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 se filtra según 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"} |
+-------------------------------+

Como alternativa, puedes usar la función STRING, que extrae una cadena JSON y muestra ese valor como un STRING SQL. Por ejemplo:

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

Además de STRING, es posible que debas extraer valores JSON y mostrarlos como otro tipo de datos SQL. Las siguientes funciones de extracción de valores están disponibles:

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

Convierte JSON de forma flexible

Puedes convertir un valor JSON en un valor SQL escalar de forma flexible y sin errores con las funciones LAX Conversion.

En el siguiente ejemplo, se muestra la potencia de estas funciones. LAX_IN64 infiere y procesa automáticamente la entrada de forma correcta. 

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

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

Extrae arrays de JSON

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

  • JSON_QUERY_ARRAY: extrae un arreglo y lo muestra como ARRAY<JSON> de JSON.
  • JSON_VALUE_ARRAY: extrae un arreglo de valores escalares y lo muestra como un ARRAY<STRING> de valores escalares.

En el siguiente ejemplo, se usa JSON_QUERY_ARRAY para extraer arreglos 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"}]                                 |
+----------------------------------------------------------------+

A fin de dividir un arreglo en sus elementos individuales, usa el operador UNNEST, que muestra una tabla con una fila para cada elemento en el arreglo. En el siguiente ejemplo, se selecciona el miembro product de cada miembro del arreglo 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 volver a agregar los valores a un arreglo de 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 los arreglos, consulta Trabaja con arreglos en GoogleSQL.

Valores null de JSON

El tipo JSON tiene un valor null especial que es diferente del NULL de SQL. 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 muestra un null JSON, porque es un valor JSON válido.
  • La función JSON_VALUE muestra el NULL de SQL, ya que el null 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       |
+------------+------------+