Trabalhar com dados JSON no GoogleSQL

Este documento descreve como criar uma tabela com uma coluna JSON, inserir dados JSON numa tabela do BigQuery e consultar dados JSON.

O BigQuery suporta nativamente dados JSON através do tipo de dados JSON.

O JSON é um formato amplamente usado que permite dados semiestruturados, porque não requer um esquema. As aplicações podem usar uma abordagem de "esquema na leitura", em que a aplicação carrega os dados e, em seguida, consulta com base em pressupostos sobre o esquema desses dados. Esta abordagem difere do tipo STRUCT no BigQuery, que requer um esquema fixo que é aplicado a todos os valores armazenados numa coluna do tipo STRUCT.

Ao usar o tipo de dados JSON, pode carregar JSON semiestruturado para o BigQuery sem fornecer um esquema para os dados JSON antecipadamente. Isto permite-lhe armazenar e consultar dados que nem sempre seguem esquemas fixos e tipos de dados. Ao carregar dados JSON como um tipo de dados JSON, o BigQuery pode codificar e processar cada campo JSON individualmente. Em seguida, pode consultar os valores dos campos e dos elementos da matriz nos dados JSON usando o operador de acesso a campos, o que torna as consultas JSON intuitivas e rentáveis.

Limitações

• Se usar uma tarefa de carregamento em lote para carregar dados JSON para uma tabela, os dados de origem têm de estar no formato CSV, Avro ou JSON. Outros formatos de carregamento em lote não são suportados.
• O tipo de dados JSON tem um limite de aninhamento de 500.
• Não pode usar o SQL antigo para consultar uma tabela que contenha tipos JSON.
• Não é possível aplicar políticas de acesso ao nível da linha em colunas JSON.

Para saber mais sobre as propriedades do tipo de dados JSON, consulte o tipo JSON.

Crie uma tabela com uma coluna JSON

Pode criar uma tabela vazia com uma coluna JSON através de SQL ou da ferramenta de linha de comandos bq.

SQL

Use a declaração CREATE TABLE e declare uma coluna com o tipo JSON.

1. Na Google Cloud consola, aceda à página BigQuery.

   Aceda ao BigQuery

2. No editor de consultas, introduza a seguinte declaração:

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

3. Clique em Executar.

Para mais informações sobre como executar consultas, consulte o artigo Execute uma consulta interativa.

bq

Use o comando bq mk e forneça um esquema de tabela com um tipo de dados JSON.

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

Não pode particionar nem agrupar uma tabela em colunas JSON, porque os operadores de igualdade e comparação não estão definidos no tipo JSON.

Crie JSON valores

Pode criar valores JSON das seguintes formas:

• Use SQL para criar um literal JSON.
• Use a função PARSE_JSON para converter um valor STRING num valor JSON.
• Use a função TO_JSON para converter um valor SQL num valor JSON.
• Use a função JSON_ARRAY para criar uma matriz JSON a partir de valores SQL.
• Use a função JSON_OBJECT para criar um objeto JSON a partir de pares de chave-valor.

Crie um valor de JSON

O exemplo seguinte insere valores JSON numa tabela:

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']));

Converta um tipo STRING no tipo JSON

O exemplo seguinte converte um valor STRING formatado em JSON através da função PARSE_JSON. O exemplo converte uma coluna de uma tabela existente num tipo JSON e guarda os resultados numa nova tabela.

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

O prefixo SAFE usado neste exemplo garante que quaisquer erros de conversão são devolvidos como valores NULL.

Converta dados esquematizados em JSON

O exemplo seguinte converte pares de chave-valor em JSON através da função 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

O resultado é o seguinte:

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

Converta um tipo SQL num tipo JSON

O exemplo seguinte converte um valor SQL STRUCT num valor JSON através da função TO_JSON:

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

O resultado é o seguinte:

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

Carregue dados JSON

Pode carregar dados JSON para uma tabela do BigQuery das seguintes formas:

• Use uma tarefa de carregamento em lote para carregar dados em colunas JSON a partir dos seguintes formatos.
  • CSV
  • Avro
  • JSON
• Use a API BigQuery Storage Write.
• Use a API tabledata.insertAll streaming antiga

Carregue a partir de ficheiros CSV

O exemplo seguinte pressupõe que tem um ficheiro CSV denominado file1.csv que contém os seguintes registos:

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

Tenha em atenção que a segunda coluna contém dados JSON codificados como uma string. Isto implica escapar corretamente as aspas para o formato CSV. No formato CSV, as aspas têm carateres de escape com a sequência de dois carateres "".

Para carregar este ficheiro através da ferramenta de linhas de comando bq, use o 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

Carregue a partir de ficheiros JSON delimitados por newline

O exemplo seguinte pressupõe que tem um ficheiro denominado file1.jsonl que contém os seguintes registos:

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

Para carregar este ficheiro através da ferramenta de linhas de comando bq, use o 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

Use a API Storage Write

Pode usar a API Storage Write para carregar dados JSON. O exemplo seguinte usa a API Storage Write cliente Python para escrever dados numa tabela com uma coluna do tipo de dados JSON.

Defina um protocolo de buffer para conter os dados de streaming serializados. Os dados JSON estão codificados como uma string. No exemplo seguinte, o campo json_col contém dados JSON.

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

Formate os dados JSON de cada linha como um 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'

Anexe as linhas ao fluxo de gravação, conforme mostrado no exemplo de código. A biblioteca de cliente processa a serialização para o formato de Protocol Buffer.

Se não conseguir formatar os dados JSON recebidos, tem de usar o método json.dumps() no seu código. Vejamos um exemplo:

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)

...

Use a API Legacy Streaming

O exemplo seguinte carrega dados JSON de um ficheiro local e faz stream para uma tabela do BigQuery com uma coluna de tipo de dados JSON denominada json_data através da API Legacy Streaming.

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 mais informações, consulte o artigo Transmitir dados para o BigQuery.

Consultar dados JSON

Esta secção descreve como usar o GoogleSQL para extrair valores de JSON. O JSON é sensível a maiúsculas e minúsculas e suporta UTF-8 nos campos e nos valores.

Os exemplos nesta secção usam a seguinte tabela:

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}
        ]
    }""");

Extraia valores como JSON

Dado um tipo JSON no BigQuery, pode aceder aos campos numa expressão JSON através do operador de acesso a campos. O exemplo seguinte devolve o campo name da coluna cart.

SELECT cart.name
FROM mydataset.table1;

+---------+
|  name   |
+---------+
| "Alice" |
| "Bob"   |
+---------+

Para aceder a um elemento de matriz, use o operador de subscrição JSON. O exemplo seguinte devolve o primeiro elemento da matriz items:

SELECT
  cart.items[0] AS first_item
FROM mydataset.table1

+-------------------------------+
|          first_item           |
+-------------------------------+
| {"price":10,"product":"book"} |
| {"price":20,"product":"pen"}  |
+-------------------------------+

Também pode usar o operador de subscrição JSON para fazer referência aos membros de um objeto JSON por nome:

SELECT cart['name']
FROM mydataset.table1;

+---------+
|  name   |
+---------+
| "Alice" |
| "Bob"   |
+---------+

Para operações de subscrito, a expressão entre parênteses retos pode ser qualquer expressão de string ou de número inteiro arbitrária, incluindo expressões não constantes:

DECLARE int_val INT64 DEFAULT 0;

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

+--------+
|  item  |
+--------+
| "food" |
| NULL   |
+--------+

Os operadores de acesso a campos e de subscrição devolvem tipos JSON, pelo que pode encadear expressões que os usam ou transmitir o resultado a outras funções que usam tipos JSON.

Estes operadores melhoram a legibilidade da funcionalidade básica da função JSON_QUERY. Por exemplo, a expressão cart.name é equivalente a JSON_QUERY(cart, "$.name").

Se não for encontrado um membro com o nome especificado no objeto JSON ou se a matriz JSON não tiver um elemento com a posição especificada, estes operadores devolvem SQL NULL.

SELECT
  cart.address AS address,
  cart.items[1].price AS item1_price
FROM
  mydataset.table1;

+---------+-------------+
| address | item1_price |
+---------+-------------+
| NULL    | NULL        |
| NULL    | 5           |
+---------+-------------+

Os operadores de igualdade e comparação não estão definidos no tipo de dados JSON. Por conseguinte, não pode usar valores JSON diretamente em cláusulas como GROUP BY ou ORDER BY. Em alternativa, use a função JSON_VALUE para extrair valores de campos como strings SQL, conforme descrito na secção seguinte.

Extraia valores como strings

A função JSON_VALUE extrai um valor escalar e devolve-o como uma string SQL. Devolve SQL NULL se cart.name não apontar para um valor escalar no JSON.

SELECT JSON_VALUE(cart.name) AS name
FROM mydataset.table1;

+-------+
| name  |
+-------+
| Alice |
+-------+

Pode usar a função JSON_VALUE em contextos que exijam igualdade ou comparação, como cláusulas WHERE e cláusulas GROUP BY. O exemplo seguinte mostra uma cláusula WHERE que filtra um valor JSON:

SELECT
  cart.items[0] AS first_item
FROM
  mydataset.table1
WHERE
  JSON_VALUE(cart.name) = 'Alice';

+-------------------------------+
| first_item                    |
+-------------------------------+
| {"price":10,"product":"book"} |
+-------------------------------+

Em alternativa, pode usar a função STRING que extrai uma string JSON e devolve esse valor como um STRING SQL. Por exemplo:

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

+--------+
| color  |
+--------+
| purple |
+--------+

Além de STRING, pode ter de extrair valores JSON e devolvê-los como outro tipo de dados SQL. Estão disponíveis as seguintes funções de extração de valores:

• STRING
• BOOL
• INT64
• FLOAT64

Para obter o tipo do valor JSON, pode usar a função JSON_TYPE.

Converta JSON de forma flexível

Pode converter um valor JSON num valor SQL escalar de forma flexível com as funções LAX conversion.

O exemplo seguinte usa a função LAX_INT64 para extrair um valor INT64 de um valor JSON.

SELECT LAX_INT64(JSON '"10"') AS id;

+----+
| id |
+----+
| 10 |
+----+

Além de LAX_INT64, pode converter de forma flexível para outros tipos de SQL para JSON com as seguintes funções:

• LAX_STRING
• LAX_BOOL
• LAX_INT64
• LAX_FLOAT64

Extraia matrizes de JSON

O JSON pode conter matrizes JSON, que não são diretamente equivalentes a um tipo ARRAY<JSON> no BigQuery. Pode usar as seguintes funções para extrair um ARRAY do BigQuery de JSON:

• JSON_QUERY_ARRAY: extrai uma matriz e devolve-a como um ARRAY<JSON> de JSON.
• JSON_VALUE_ARRAY: extrai uma matriz de valores escalares e devolve-a como um ARRAY<STRING> de valores escalares.

O exemplo seguinte usa JSON_QUERY_ARRAY para extrair matrizes 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 uma matriz nos respetivos elementos individuais, use o operador UNNEST que devolve uma tabela com uma linha para cada elemento na matriz. O exemplo seguinte seleciona o membro product de cada membro da 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     |
+----+---------+

O exemplo seguinte é semelhante, mas usa a função ARRAY_AGG para agregar os valores novamente numa 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 mais informações sobre matrizes, consulte o artigo Trabalhar com matrizes no GoogleSQL.

Valores nulos JSON

O tipo JSON tem um valor null especial que é diferente do SQL NULL. Um JSON null não é tratado como um valor SQL NULL, como mostra o seguinte exemplo.

SELECT JSON 'null' IS NULL;

+-------+
| f0_   |
+-------+
| false |
+-------+

Quando extrai um campo JSON com um valor null, o comportamento depende da função:

• A função JSON_QUERY devolve um JSON null, porque é um JSON valor válido.
• A função JSON_VALUE devolve o SQL NULL, porque o JSON null não é um valor escalar.

O exemplo seguinte mostra os diferentes comportamentos:

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

Aviso: este comportamento não se aplica a valores JSON armazenados num tipo STRING. Quando é transmitido um valor STRING, a função JSON_QUERY devolve valores SQL NULL em vez de valores JSON null.