Como trabalhar com dados JSON no GoogleSQL

Neste documento, você saberá como criar uma tabela com uma coluna JSON, inserir dados JSON em uma tabela do BigQuery e consultar dados JSON.

O BigQuery tem compatibilidade nativa com dados JSON usando o tipo de dados JSON.

JSON é um formato amplamente utilizado que permite dados semiestruturados, porque não requer um esquema. Os aplicativos podem usar uma abordagem de "esquema em leitura", em que o aplicativo ingere os dados e consulta com base em suposições sobre o esquema desses dados. Essa abordagem é diferente do tipo STRUCT no BigQuery, que exige um esquema fixo aplicado a todos os valores armazenados em uma coluna do tipo STRUCT.

Ao usar o tipo de dados JSON, é possível ingerir JSON semiestruturado no BigQuery sem fornecer um esquema antecipado para os dados JSON. Isso permite armazenar e consultar dados que nem sempre aderem a esquemas e tipos de dados fixos. Ao ingerir dados JSON como um tipo de dados JSON, o BigQuery pode codificar e processar cada campo JSON individualmente. Em seguida, consulte os valores de campos e elementos de matriz nos dados JSON usando o operador de acesso a campos, que facilita o uso e o custo-benefício das consultas JSON.

Limitações

  • Se você usar um job de carregamento em lote para ingerir dados JSON em uma tabela, os dados de origem precisarão 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 é possível usar um SQL legado para consultar uma tabela que contém tipos JSON.
  • Não é possível aplicar políticas de acesso no nível da linha em colunas JSON.

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

Criar uma tabela com uma coluna JSON

É possível criar uma tabela vazia com uma coluna JSON usando SQL ou a ferramenta de linha de comando.

SQL

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

  1. No Console do Google Cloud, acesse a página BigQuery.

    Ir para o BigQuery

  2. No editor de consultas, digite a seguinte instrução:

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

  3. Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar 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 é possível particionar ou agrupar uma tabela em colunas JSON, porque os operadores de igualdade e comparação não são definidos no tipo JSON.

Criar valores JSON

É possível criar valores JSON das seguintes maneiras:

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

Criar um valor JSON

O exemplo a seguir insere valores JSON em uma 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']));

Converter um tipo STRING em JSON

O exemplo a seguir converte um valor STRING formatado em JSON usando a função PARSE_JSON. O exemplo converte uma coluna de uma tabela atual em um tipo JSON e armazena os resultados em uma 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 todos os erros de conversão sejam retornados como valores NULL.

Converter dados esquematizados para JSON

O exemplo a seguir converte pares de chave-valor em JSON usando a 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"} |
+----------------------------------+

Converter um tipo SQL para o tipo JSON

O exemplo a seguir converte um valor STRUCT do SQL em um tipo JSON usando a 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} |
+--------------------------------+

Ingerir dados JSO

É possível ingerir dados JSON em uma tabela do BigQuery das seguintes maneiras:

Carregar de arquivos CSV

No exemplo a seguir, pressupomos que você tenha um arquivo CSV chamado file1.csv que contenha os seguintes registros:

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

Observe que a segunda coluna contém os dados JSON codificados como uma string. Isso envolve o escape correto das aspas no formato CSV. No formato CSV, as aspas são escapadas usando a sequência de dois caracteres "".

Para carregar esse arquivo usando a ferramenta de linha 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

Carregar de arquivos JSON delimitados por nova linha

No exemplo a seguir, pressupomos que você tenha um arquivo chamado file1.jsonl que contenha os seguintes registros:

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

Para carregar esse arquivo usando a ferramenta de linha 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

Usar a API Storage Write

Use a API Storage Write para ingerir dados JSON. O exemplo a seguir usa a API Storage Write cliente Python para gravar dados em uma tabela com uma coluna de tipo de dados JSON.

Defina um buffer de protocolo para armazenar os dados de streaming serializados. Os dados JSON são codificados como uma string. No exemplo a seguir, 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 de 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 stream de gravação, conforme mostrado no exemplo de código. A biblioteca de cliente gerencia a serialização do formato de buffer de protocolo.

Se não for possível formatar os dados JSON recebidos, use o método json.dumps() no código. Veja 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)

...

Usar a API de streaming legada

O exemplo a seguir carrega dados JSON de um arquivo local e os transmite a uma tabela do BigQuery com uma coluna de tipo de dados JSON chamada json_data usando a 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 Como fazer streaming de dados para o BigQuery.

Consultar dados JSON

Esta seção descreve como usar o GoogleSQL para extrair valores do JSON. O JSON diferencia maiúsculas de minúsculas e é compatível com UTF-8 nos campos e valores.

Os exemplos desta seção usam a tabela a seguir:

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

Extrair valores como JSON

Dado um tipo de JSON no BigQuery, é possível acessar os campos em uma expressão JSON usando o operador de acesso ao campo. O exemplo a seguir retorna o campo name da coluna cart.

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

Para acessar um elemento de matriz, use o operador de subscrito JSON. O exemplo a seguir retorna 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 é possível usar o operador de script JSON para fazer referência aos membros de um objeto JSON pelo nome:

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

Para operações de subscrito, a expressão dentro dos colchetes pode ser qualquer string arbitrária ou expressão inteira, 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 ao campo e subscrito retornam tipos JSON para que você possa encadear expressões que os usem ou transmitam o resultado para outras funções que usam tipos JSON.

Esses operadores são o açúcar sintático para a função JSON_QUERY. Por exemplo, a expressão cart.name é equivalente a JSON_QUERY(cart, "$.name").

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

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 são definidos no tipo de dados JSON. Portanto, não é possível usar valores JSON diretamente em cláusulas como GROUP BY ou ORDER BY. Em vez disso, use a função JSON_VALUE para extrair valores de campo como strings SQL, conforme descrito na próxima seção.

Extrair valores como strings

A função JSON_VALUE extrai um valor escalar e o retorna como uma string SQL. Ele retornará 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 |
+-------+

Use a função JSON_VALUE em contextos que exigem igualdade ou comparação, como cláusulas WHERE e GROUP BY. O exemplo a seguir mostra uma cláusula WHERE que filtra por 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"} |
+-------------------------------+

Outra opção é usar a função STRING, que extrai uma string JSON e retorna esse valor como um STRING do SQL. Exemplo:

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

Além de STRING, talvez seja necessário extrair valores JSON e retorná-los como outro tipo de dados SQL. As seguintes funções de extração de valor estão disponíveis:

Para conferir o tipo de valor JSON, use a função JSON_TYPE.

Converter JSON com flexibilidade

É possível converter um valor JSON em um valor SQL escalar de maneira flexível e livre de erros com as funções LAX Conversion.

O exemplo a seguir demonstra o poder dessas funções. LAX_IN64 infere e processa a entrada automaticamente. 

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

Além de LAX_IN64, é possível converter para outros tipos de SQL de maneira flexível para JSON com as seguintes funções:

Extrair matrizes do JSON

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

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

O exemplo a seguir 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 elementos individuais dela, use o operador UNNEST, que retorna uma tabela com uma linha para cada elemento na matriz. O exemplo a seguir 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 próximo exemplo é semelhante, mas usa a função ARRAY_AGG para agregar os valores de volta em uma 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 Como trabalhar com matrizes no GoogleSQL.

JSON nulo

O tipo JSON tem um valor null especial que é diferente do NULL SQL. Um JSON null não é tratado como um valor SQL NULL, como mostrado no exemplo a seguir.

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

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

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

O exemplo a seguir 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       |
+------------+------------+