Utilizzo di dati JSON in GoogleSQL
BigQuery supporta in modo nativo i dati JSON utilizzando
Tipo di dati JSON
.
Questo documento descrive come creare una tabella con una colonna JSON
, inserire JSON
in una tabella BigQuery ed eseguire query sui dati JSON.
Panoramica
JSON è un formato ampiamente usato che consente di inserire dati semistrutturati,
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 ed eseguire query su dati che non sempre rispettano gli schemi fissi
e tipi di dati. 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 in batch.
- 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
.
Nella console Google Cloud, vai alla pagina BigQuery.
Nell'editor query, inserisci la seguente istruzione:
CREATE TABLE mydataset.table1( id INT64, cart JSON );
Fai clic su
Esegui.
Per ulteriori informazioni su come eseguire le query, vedi 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 eseguire il partizionamento o il cluster di una tabella in JSON
colonne perché l'uguaglianza
e gli operatori di confronto non sono definiti nel tipo JSON
.
Crea valori JSON
Puoi creare valori JSON
nei seguenti modi:
- Utilizza SQL per creare un valore letterale
JSON
. - Utilizza la
PARSE_JSON
per convertire un valoreSTRING
in un valoreJSON
. - Utilizza la
TO_JSON
per convertire un valore SQL in un valoreJSON
. - Utilizza la
JSON_ARRAY
per creare un array JSON da valori SQL. - Utilizza la
JSON_OBJECT
per creare un oggetto JSON da coppie chiave/valore.
Crea un valore JSON
L'esempio seguente inserisce i 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
utilizzato in questo esempio garantisce che eventuali errori di conversione vengano restituiti come NULL
e i relativi valori.
Converti 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 SQL STRUCT
in un valore JSON
utilizzando
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 dati JSON
Puoi importare dati JSON in una tabella BigQuery nel seguente modi:
- Utilizza un job di caricamento in batch per eseguire il caricamento in
JSON
colonne dai seguenti formati. - Utilizza l'API BigQuery Storage Scrivi.
- Utilizza la versione precedente
API
tabledata.insertAll
streaming
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 interpretati letteralmente utilizzando la sequenza di due caratteri ""
.
Per caricare questo file utilizzando lo strumento a riga di comando bq, usa la
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
Carica da file JSON delimitato da nuova riga
L'esempio seguente presuppone che tu abbia 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, 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
Utilizza l'API StorageWrite
Puoi utilizzare l'API Storage Scrivi per importare i dati JSON. L'esempio seguente utilizza l'API StorageWrite client Python.
Definisci un buffer di protocollo per contenere i flussi di dati serializzati. I dati JSON
è codificato come una 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 di buffer di protocollo.
Usa l'API Streaming legacy
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, vedi Flusso 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 un 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 di 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 in pedice, l'espressione all'interno delle parentesi può essere qualsiasi stringa arbitraria o espressione di numeri interi, incluse 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 rappresentano lo zucchero sintattico per
JSON_QUERY
personalizzata. Ad esempio, l'espressione
cart.name
equivale a JSON_QUERY(cart, "$.name")
.
Se un membro con il nome specificato non viene trovato nell'oggetto JSON o se
l'array JSON non ha un elemento con la posizione specificata,
questi operatori restituiscono SQL NULL
.
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
.
Pertanto, 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 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 l'uguaglianza o
di confronto, come le clausole WHERE
e 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 di valori:
Per ottenere il tipo di valore JSON
, puoi utilizzare JSON_TYPE
personalizzata.
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 mostra la potenza di queste funzioni. LAX_IN64
deduce ed elabora automaticamente l'input.
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
:
Estrai gli 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 comeARRAY<JSON>
di JSON.JSON_VALUE_ARRAY
: estrae un array di valori scalari e lo restituisce comeARRAY<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. La
L'esempio seguente seleziona il membro product
da ciascun membro di items
array:
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, Uso degli array in GoogleSQL.
Null JSON
Il tipo JSON
ha un valore null
speciale diverso dall'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 unnull
JSON, perché è un valoreJSON
valido valore. - La funzione
JSON_VALUE
restituisce l'SQLNULL
, perché il codice JSONnull
non è un 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 | +------------+------------+