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 tipo di dato JSON.

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

Utilizzando il tipo di dati JSON, puoi caricare file JSON semistrutturati in BigQuery senza fornire in anticipo uno schema per i dati JSON. In questo modo puoi archiviare e eseguire query su dati che non rispettano sempre schemi e tipi di dati fissi. Se importi dati JSON come tipo di dati JSON, BigQuery può codificare ed elaborare ogni campo JSON singolarmente. Tu può quindi eseguire query sui valori di campi ed elementi array all'interno dei dati JSON usando l'operatore di accesso sul campo, che rende le query JSON intuitive e costano in modo efficace.

Limitazioni

  • Se utilizzi un job di caricamento in batch per importare Dati JSON in una tabella, i dati di origine devono essere in formato CSV, Avro o JSON. Non sono supportati altri formati di caricamento collettivo.
  • 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 JSON tipi.
  • I criteri di accesso a livello di riga non possono essere applicati a JSON colonne.

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

Crea una tabella con una colonna JSON

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

SQL

Utilizza la CREATE TABLE e dichiarare una colonna di tipo JSON.

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

    Vai a BigQuery

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

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

  3. Fai clic su Esegui.

Per ulteriori informazioni su come eseguire le query, consulta Eseguire una query interattiva.

bq

Utilizzare il comando bq mk 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 a colonne JSON, perché gli operatori di uguaglianza e di confronto non sono definiti per il tipo JSON.

Crea valori JSON

Puoi creare valori JSON nei seguenti modi:

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

Converti un tipo STRING in tipo JSON

L'esempio seguente converte un valore STRING in formato JSON utilizzando il metodo PARSE_JSON personalizzata. L'esempio converte una colonna da una tabella esistente a un tipo JSON e archivia 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 prefisso SAFE usato in questo esempio garantisce che eventuali errori di conversione vengano restituiti come valori NULL.

Convertire i dati schematizzati in JSON

L'esempio seguente converte le coppie chiave-valore in JSON utilizzando il metodo JSON_OBJECT personalizzata.

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

Converti un tipo SQL in tipo JSON

L'esempio seguente converte un valore STRUCT SQL 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} |
+--------------------------------+

Importa i dati JSON

Puoi importare dati JSON in una tabella BigQuery nel seguente modi:

Carica da file CSV

L'esempio seguente presuppone che tu abbia 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. Questo prevede l'utilizzo corretto delle virgolette per il formato CSV. In formato CSV, le virgolette vengono sostituite utilizzando la sequenza di due caratteri "".

Per caricare questo file utilizzando lo strumento a riga di comando bq, utilizza il 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

Caricamento da file JSON delimitati da nuova riga

Nell'esempio seguente si presuppone che tu abbia un file denominato file1.jsonl che contenga 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, usa la 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

Utilizzare l'API Storage Scrivi

Puoi utilizzare l'API Storage Write per eseguire l'importazione di dati JSON. L'esempio seguente utilizza il client Python dell'API Storage Write.

Definisci un buffer di protocollo per contenere i flussi di dati serializzati. I dati JSON vengono 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 in esempio di codice. La libreria client gestisce la serializzazione nel formato del buffer di protocollo.

Utilizzare l'API Streaming precedente

L'esempio seguente carica i dati JSON da un file locale e li invia in modalità flusso a BigQuery utilizzando API di streaming legacy.

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 string.
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 ulteriori informazioni, consulta Eseguire lo streaming di dati in BigQuery.

Query su dati JSON

Questa sezione descrive come utilizzare GoogleSQL per estrarre valori da JSON. JSON è sensibile alle maiuscole e supporta UTF-8 sia nei campi che 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}
        ]
    }""");

Estrai i valori come JSON

Dato il tipo JSON in BigQuery, puoi accedere ai campi in una Espressione JSON utilizzando l'operatore di accesso al campo. 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 Operatore JSON Pscript. 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 pedice JSON per fare riferimento ai membri di un file JSON per nome:

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

Per le operazioni di pedice, l'espressione all'interno delle parentesi può essere qualsiasi expressione di stringa o numero intero 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 al campo e di pedice restituiscono entrambi tipi di JSON, quindi puoi concatenare espressioni che le usano o passano il risultato ad altre funzioni che utilizzano JSON di testo.

Questi operatori sono sintassi per la funzione JSON_QUERY. Ad esempio, l'espressione cart.name è equivalente a JSON_QUERY(cart, "$.name").

Se nell'oggetto JSON non viene trovato un membro con il nome specificato 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 nel tipo di dati JSON. Di conseguenza, non puoi utilizzare i valori JSON direttamente nelle 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 funzione JSON_VALUE estrae un valore scalare e lo restituisce come stringa SQL. Restituisce SQL NULL se cart.name non punta a un valore scalare nel JSON.

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

Puoi utilizzare la funzione JSON_VALUE in contesti che richiedono uguaglianza o confronto, ad esempio clausole WHERE e clausole GROUP BY. Le seguenti esempio 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 che estrae una stringa JSON e restituisce quel 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 altri dati SQL di testo. Sono disponibili le seguenti funzioni di estrazione dei valori:

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

Converti JSON in modo flessibile

Puoi convertire un valore JSON in un valore SQL scalare in modo flessibile e senza errori con LAX Conversion funzioni.

L'esempio seguente dimostra la potenza di queste funzioni. LAX_IN64 deduce e gestisce automaticamente l'input in modo corretto. 

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

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

Estrarre array da JSON

JSON può contenere array JSON, che non sono direttamente equivalenti a un Digita ARRAY<JSON> in BigQuery. Puoi utilizzare le seguenti opzioni per estrarre un valore 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 suddividere un array nei suoi singoli elementi, utilizza la classe UNNEST , che restituisce una tabella con una riga per ogni elemento nell'array. L'esempio seguente seleziona l'elemento product da ogni elemento 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 il parametro ARRAY_AGG per aggregare 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 ulteriori informazioni sugli array, consulta Utilizzo degli array in GoogleSQL.

Valori null JSON

Il tipo JSON ha un valore null speciale diverso da quello SQL NULL. Un null JSON non viene considerato un valore NULL SQL, come riportato di seguito come mostra un esempio.

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

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

  • La funzione JSON_QUERY restituisce un null JSON, perché è un valore JSON valido valore.
  • La funzione JSON_VALUE restituisce l'SQL NULL, perché il codice JSON null non è un come 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       |
+------------+------------+