Utilizzo dei dati JSON in GoogleSQL

Questo documento descrive come creare una tabella con una colonna JSON, inserire dati JSON in una tabella BigQuery ed eseguire query sui dati JSON.

BigQuery supporta in modo nativo i dati JSON utilizzando il JSON tipo di dati.

JSON è un formato ampiamente utilizzato che consente di utilizzare dati semistrutturati, perché non richiede uno schema. Le applicazioni possono utilizzare un approccio "schema-on-read", in cui l'applicazione inserisce i dati ed esegue query in base alle ipotesi sullo schema di questi dati. Questo approccio è diverso dal tipo STRUCT in BigQuery, che richiede uno schema fisso applicato a tutti i valori archiviati in una colonna di tipo STRUCT.

Utilizzando il tipo di dati JSON, puoi caricare JSON semistrutturati in BigQuery senza fornire in anticipo uno schema per i dati JSON. In questo modo puoi archiviare ed eseguire query sui dati che non rispettano sempre schemi e tipi di dati fissi. Inserendo i dati JSON come tipo di dati JSON, BigQuery può codificare ed elaborare ogni campo JSON singolarmente. Puoi quindi eseguire query sui valori dei campi e degli elementi dell'array all'interno dei dati JSON utilizzando l'operatore di accesso ai campi, il che rende le query JSON intuitive ed economiche.

Limitazioni

  • Se utilizzi un job di caricamento batch per inserire dati JSON in una tabella, i dati di origine devono essere in formato CSV, Avro o JSON. Gli altri formati di caricamento batch non sono supportati.
  • Il tipo di dati JSON ha un limite di nidificazione di 500.
  • Non puoi utilizzare SQL precedente per eseguire query su una tabella che contiene tipi JSON.
  • Le norme di accesso a livello di riga non possono essere applicate alle colonne JSON.

Per scoprire di più sulle proprietà del tipo di dati JSON, consulta JSON tipo.

Creare una tabella con una colonna JSON

Puoi creare una tabella vuota con una colonna JSON utilizzando SQL o lo strumento a riga di comando bq.

SQL

Utilizza l' CREATE TABLE istruzione e dichiara una colonna con il tipo JSON.

  1. Nella Google Cloud console, vai alla pagina BigQuery.

    Vai a BigQuery

  2. Nell'editor di query, inserisci la seguente istruzione:

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

  3. Fai clic su Esegui.

Per saperne di più su come eseguire le query, consulta Eseguire una query interattiva.

bq

Utilizza il bq mk comando e fornisci uno schema di tabella con un tipo di dati JSON.

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

Non puoi partizionare o raggruppare una tabella in base alle colonne JSON, perché gli operatori di uguaglianza e confronto non sono definiti per il tipo JSON.

Creare valori JSON

Puoi creare valori JSON nei seguenti modi:

  • Utilizza SQL per creare un JSON valore letterale.
  • Utilizza la PARSE_JSON funzione per convertire un valore STRING in un valore JSON.
  • Utilizza la TO_JSON funzione per convertire un valore SQL in un valore JSON.
  • Utilizza la JSON_ARRAY funzione per creare un array JSON da valori SQL.
  • Utilizza la JSON_OBJECT funzione per creare un oggetto JSON da coppie chiave-valore.

Creare un valore JSON

L'esempio seguente inserisce valori JSON in una tabella:

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

Convertire un tipo STRING in un tipo JSON

L'esempio seguente converte un valore STRING in formato JSON utilizzando la PARSE_JSON funzione. L'esempio converte una colonna da una tabella esistente in un tipo JSON e salva i risultati in una nuova tabella.

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

Il SAFE prefisso utilizzato in questo esempio garantisce che eventuali errori di conversione vengano restituiti come NULL valori.

Convertire i dati schematizzati in JSON

L'esempio seguente converte le coppie chiave-valore in JSON utilizzando la JSON_OBJECT funzione.

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

Il risultato è il seguente:

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

Convertire un tipo SQL in un tipo JSON

L'esempio seguente converte un valore SQL STRUCT in un valore JSON utilizzando la funzione TO_JSON:

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

Il risultato è il seguente:

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

Inserire dati JSON

Puoi inserire dati JSON in una tabella BigQuery nei seguenti modi:

Caricare da file CSV

L'esempio seguente presuppone che tu disponga di un file CSV denominato file1.csv che contiene i seguenti record:

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

Tieni presente che la seconda colonna contiene dati JSON codificati come stringa. Ciò comporta l'escape corretto delle virgolette per il formato CSV. Nel formato CSV, le virgolette vengono sottoposte a escape utilizzando la sequenza di due caratteri "".

Per caricare questo file utilizzando lo strumento a riga di comando bq, utilizza il bq load comando:

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

Caricare da file JSON delimitati da nuova riga

L'esempio seguente presuppone che tu disponga di un file denominato file1.jsonl che contiene i seguenti record:

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

Per caricare questo file utilizzando lo strumento a riga di comando bq, utilizza il bq load comando:

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

Utilizzare l'API Storage Write (gRPC)

Puoi utilizzare l'API Storage Write (gRPC) per inserire dati JSON. L'esempio seguente utilizza il client Python dell'API Storage Write (gRPC) per scrivere dati in una tabella con una colonna di tipo di dati JSON.

Definisci un buffer di protocollo per contenere i dati di streaming serializzati. I dati JSON sono codificati come stringa. Nell'esempio seguente, il campo json_col contiene dati JSON.

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

Formatta i dati JSON per ogni riga come valore 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'

Aggiungi le righe al flusso di scrittura come mostrato nell' esempio di codice. La libreria client gestisce la serializzazione nel formato del buffer di protocollo.

Se non riesci a formattare i dati JSON in entrata, devi utilizzare il metodo json.dumps() nel codice. Ecco un esempio:

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)

...

Utilizzare l'API Storage Write (REST)

L'esempio seguente carica i dati JSON da un file locale e li trasmette in streaming a una tabella BigQuery con una colonna di tipo di dati JSON denominata json_data utilizzando l' API Storage Write (REST).

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

Per saperne di più, consulta Inserire dati in streaming in BigQuery.

Eseguire query sui dati JSON

Questa sezione descrive come utilizzare GoogleSQL per estrarre i valori da JSON. JSON fa distinzione tra maiuscole e minuscole e supporta UTF-8 sia nei campi sia nei valori.

Gli esempi in questa sezione utilizzano la seguente tabella:

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

Estrarre i valori come JSON

Dato un tipo JSON in BigQuery, puoi accedere ai campi in un' espressione JSON utilizzando l'operatore di accesso ai campi. L'esempio seguente restituisce il campo name della colonna cart.

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

Per accedere a un elemento dell'array, utilizza l' operatore di sottoscrizione JSON. L'esempio seguente restituisce il primo elemento dell'array items:

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

Puoi anche utilizzare l'operatore di sottoscrizione JSON per fare riferimento ai membri di un oggetto JSON per nome:

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

Per le operazioni di sottoscrizione, l'espressione all'interno delle parentesi quadre può essere qualsiasi espressione stringa o intera arbitraria, incluse le espressioni non costanti:

DECLARE int_val INT64 DEFAULT 0;

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

Gli operatori di accesso ai campi e di sottoscrizione restituiscono entrambi tipi JSON, quindi puoi concatenare le espressioni che li utilizzano o passare il risultato ad altre funzioni che accettano tipi JSON.

Questi operatori migliorano la leggibilità della funzionalità di base della JSON_QUERY funzione. Ad esempio, l'espressione cart.name è equivalente a JSON_QUERY(cart, "$.name").

Se non viene trovato un membro con il nome specificato nell'oggetto JSON o se l'array JSON non ha un elemento con la posizione specificata, questi operatori restituiscono NULL SQL.

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

Gli operatori di uguaglianza e confronto non sono definiti per il tipo di dati JSON. Di conseguenza, non puoi utilizzare direttamente i valori JSON in clausole come GROUP BY o ORDER BY. Utilizza invece la funzione JSON_VALUE per estrarre i valori dei campi come stringhe SQL, come descritto nella sezione successiva.

Estrarre i valori come stringhe

La JSON_VALUE funzione estrae un valore scalare e lo restituisce come stringa SQL. Restituisce NULL SQL se cart.name non punta a un valore scalare in JSON.

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

Puoi utilizzare la funzione JSON_VALUE nei contesti che richiedono uguaglianza o confronto, ad esempio le clausole WHERE e GROUP BY. L'esempio seguente mostra una clausola WHERE che filtra in base a un valore JSON:

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

In alternativa, puoi utilizzare la STRING funzione che estrae una stringa JSON e restituisce il valore come STRING SQL. Ad esempio:

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

Oltre a STRING, potresti dover estrarre i valori JSON e restituirli come un altro tipo di dati SQL. Sono disponibili le seguenti funzioni di estrazione dei valori:

Per ottenere il tipo di valore JSON, puoi utilizzare la JSON_TYPE funzione.

Convertire in modo flessibile JSON

Puoi convertire un valore JSON in un valore scalare SQL in modo flessibile con LAX conversion funzioni.

L'esempio seguente utilizza la funzione LAX_INT64 per estrarre un valore INT64 da un valore JSON. 

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

Oltre a LAX_INT64, puoi convertire in modo flessibile altri tipi SQL in JSON con le seguenti funzioni:

Estrarre array da JSON

JSON può contenere array JSON, che non sono direttamente equivalenti a un ARRAY<JSON> tipo in BigQuery. Puoi utilizzare le seguenti funzioni per estrarre un ARRAY BigQuery da JSON:

  • JSON_QUERY_ARRAY: estrae un array e lo restituisce come ARRAY<JSON> di JSON.
  • JSON_VALUE_ARRAY: estrae un array di valori scalari e lo restituisce come ARRAY<STRING> di valori scalari.

L'esempio seguente utilizza JSON_QUERY_ARRAY per estrarre gli array 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"}]                                 |
+----------------------------------------------------------------+

Per dividere un array nei suoi singoli elementi, utilizza l' UNNEST operatore, che restituisce una tabella con una riga per ogni elemento dell'array. L'esempio seguente seleziona il membro product da ogni membro dell'array 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     |
+----+---------+

L'esempio successivo è simile, ma utilizza la ARRAY_AGG funzione per aggregare nuovamente i valori in un array 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"]         |
+----+-----------------+

Per saperne di più sugli array, consulta Utilizzo degli array in GoogleSQL.

Valori nulli JSON

Il tipo JSON ha un valore null speciale diverso da NULL SQL. Un valore null JSON non viene trattato come un valore NULL SQL, come mostra l'esempio seguente.

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

Quando estrai un campo JSON con un valore null, il comportamento dipende dalla funzione:

  • La funzione JSON_QUERY restituisce un valore null JSON, perché è un valore JSON valido.
  • La funzione JSON_VALUE restituisce NULL SQL, perché null JSON non è un valore scalare.

L'esempio seguente mostra i diversi comportamenti:

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