Mit JSON-Daten in GoogleSQL arbeiten

BigQuery unterstützt nativ JSON-Daten mit dem Datentyp JSON.

In diesem Dokument wird gezeigt, wie Sie eine Tabelle mit einer JSON-Spalte erstellen, JSON-Daten in eine BigQuery-Tabelle einfügen und JSON-Daten abfragen.

Überblick

JSON ist ein weit verbreitetes Format, das semistrukturierte Daten ermöglicht, da es kein Schema erfordert. Anwendungen können einen „Schema-on-Read“-Ansatz verwenden, bei dem die Anwendung die Daten aufnimmt und dann basierend auf Annahmen über das Schema dieser Daten Abfragen durchführt. Dieser Ansatz unterscheidet sich vom Typ STRUCT in BigQuery, der ein festes Schema erfordert, das für alle Werte erzwungen wird, die in einer Spalte vom Typ STRUCT gespeichert sind.

Mit dem Datentyp JSON können Sie semistrukturierte JSON-Daten in BigQuery laden, ohne im Voraus ein Schema für die JSON-Daten bereitzustellen. Dadurch können Sie Daten speichern und abfragen, die nicht immer den festgelegten Schemas und Datentypen entsprechen. Durch die Aufnahme von JSON-Daten als JSON-Datentyp kann BigQuery jedes JSON-Feld einzeln codieren und verarbeiten. Sie können dann die Werte der Felder und Arrayelemente innerhalb der JSON-Daten mit dem Operator für den Feldzugriff abfragen, was JSON-Abfragen intuitiv und kostengünstig macht.

Beschränkungen

  • Wenn Sie einen Batch-Ladejob verwenden, um JSON-Daten in eine Tabelle aufzunehmen, müssen die Quelldaten im CSV-, Avro- oder JSON-Format vorliegen. Andere Batch-Ladeformate werden nicht unterstützt.
  • Der Datentyp JSON hat ein Verschachtelungslimit von 500.
  • Sie können keine Legacy-SQL verwenden, um eine Tabelle mit JSON-Typen abzufragen.
  • Zugriffsrichtlinien auf Zeilenebene können nicht auf JSON-Spalten angewendet werden.

Weitere Informationen zu den Attributen des JSON-Datentyps finden Sie unter JSON-Typ.

Tabelle mit einer JSON-Spalte erstellen

Sie können eine leere Tabelle mit einer JSON-Spalte mithilfe von SQL oder des bq-Befehlszeilentools erstellen.

SQL

Verwenden Sie die Anweisung CREATE TABLE und deklarieren Sie eine Spalte mit dem Typ JSON.

  1. Öffnen Sie in der Google Cloud Console die Seite BigQuery.

    BigQuery aufrufen

  2. Geben Sie im Abfrageeditor die folgende Anweisung ein:

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

  3. Klicken Sie auf Ausführen.

Informationen zum Ausführen von Abfragen finden Sie unter Interaktive Abfrage ausführen.

bq

Verwenden Sie den Befehl bq mk und geben Sie ein Tabellenschema mit dem Datentyp JSON an.

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

Sie können keine Tabelle in JSON-Spalten partitionieren oder gruppieren, da die Gleichheits- und Vergleichsoperatoren nicht für den Typ JSON definiert sind.

JSON-Werte erstellen

JSON-Werte können so erstellt werden:

  • Verwenden Sie SQL, um ein JSON-Literal zu erstellen.
  • Verwenden Sie die Funktion PARSE_JSON, um einen STRING-Wert in einen JSON-Wert zu konvertieren.
  • Mit der Funktion TO_JSON können Sie einen SQL-Wert in einen JSON-Wert konvertieren.
  • Mit der Funktion JSON_ARRAY können Sie ein JSON-Array aus SQL-Werten erstellen.
  • Mit der Funktion JSON_OBJECT können Sie ein JSON-Objekt aus Schlüssel/Wert-Paaren erstellen.

Einen JSON-Wert erstellen

Im folgenden Beispiel werden JSON-Werte in eine Tabelle eingefügt:

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

STRING-Typ in JSON-Typ konvertieren

Im folgenden Beispiel wird ein JSON-formatierter STRING-Wert mithilfe der Funktion PARSE_JSON konvertiert. In diesem Beispiel wird eine Spalte aus einer vorhandenen Tabelle in einen JSON-Typ konvertiert und die Ergebnisse in einer neuen Tabelle gespeichert.

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

Das in diesem Beispiel verwendete Präfix SAFE sorgt dafür, dass alle Konvertierungsfehler als NULL-Werte zurückgegeben werden.

Schematisierte Daten in JSON konvertieren

Im folgenden Beispiel werden Schlüssel/Wert-Paare mit der Funktion JSON_OBJECT in das JSON-Format konvertiert.

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

Das Ergebnis lautet:

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

SQL-Typ in JSON-Typ konvertieren

Im folgenden Beispiel wird ein SQL-STRUCT-Wert mithilfe der Funktion TO_JSON in einen JSON-Wert konvertiert:

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

Das Ergebnis lautet:

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

JSON-Daten aufnehmen

Sie können JSON-Daten auf folgende Weise in eine BigQuery-Tabelle aufnehmen:

Aus CSV-Dateien laden

Im folgenden Beispiel wird davon ausgegangen, dass Sie eine CSV-Datei namens file1.csv haben, die die folgenden Einträge enthält:

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

Beachten Sie, dass die zweite Spalte JSON-Daten enthält, die als String codiert sind. Dies umfasst das korrekte Maskieren von Anführungszeichen für das CSV-Format. Im CSV-Format werden Anführungszeichen mithilfe der Zwei-Zeichen-Sequenz "" maskiert.

Wenn Sie diese Datei mit dem bq-Befehlszeilentool laden möchten, verwenden Sie den Befehl 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

Aus durch Zeilenumbruch getrennten JSON-Dateien laden

Im folgenden Beispiel wird davon ausgegangen, dass Sie eine Datei namens file1.jsonl haben, die die folgenden Einträge enthält:

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

Wenn Sie diese Datei mit dem bq-Befehlszeilentool laden möchten, verwenden Sie den Befehl 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

Storage Write API verwenden

Sie können die Storage Write API verwenden, um JSON-Daten aufzunehmen. Im folgenden Beispiel wird der Python-Client der Storage Write API verwendet.

Definieren Sie einen Protokollpuffer für die serialisierten Streamingdaten. Die JSON-Daten werden als String codiert. Im folgenden Beispiel enthält das Feld json_col JSON-Daten.

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

Formatieren Sie die JSON-Daten für jede Zeile als STRING-Wert:

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'

Hängen Sie die Zeilen an den Schreibstream an, wie im Codebeispiel gezeigt. Die Clientbibliothek verarbeitet die Serialisierung in das Protokollpufferformat.

Legacy-Streaming-API verwenden

Im folgenden Beispiel werden JSON-Daten aus einer lokalen Datei geladen und mithilfe der Legacy-Streaming API an BigQuery gestreamt.

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

Weitere Informationen finden Sie unter Daten in BigQuery streamen.

JSON-Daten abfragen

In diesem Abschnitt wird beschrieben, wie Sie mithilfe von GoogleSQL Werte aus dem JSON-Format extrahieren. Bei JSON wird zwischen Groß- und Kleinschreibung unterschieden und sie unterstützt UTF-8 sowohl in Feldern als auch in Werten.

In den Beispielen in diesem Abschnitt wird die folgende Tabelle verwendet:

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

Werte als JSON extrahieren

Bei einem JSON-Typ in BigQuery können Sie mit dem Operator für den Feldzugriff auf die Felder in einem JSON-Ausdruck zugreifen. Im folgenden Beispiel wird das Feld name der Spalte cart zurückgegeben.

SELECT cart.name
FROM mydataset.table1;

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

Verwenden Sie den JSON-Skriptoperator, um auf ein Arrayelement zuzugreifen. Im folgenden Beispiel wird das erste Element des Arrays items zurückgegeben:

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

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

Sie können auch den JSON-Skriptoperator verwenden, um auf die Mitglieder eines JSON-Objekts anhand des Namens zu verweisen:

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

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

Für Skriptvorgänge kann der Ausdruck in den Klammern ein beliebiger String- oder Ganzzahlausdruck sein, einschließlich nicht konstanter Ausdrücke:

DECLARE int_val INT64 DEFAULT 0;

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

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

Operatoren für Feldzugriff und Startskripts geben beide JSON-Typen zurück, sodass Sie Ausdrücke verketten können, die sie verwenden, oder das Ergebnis an andere Funktionen übergeben, die JSON-Typen verwenden.

Diese Operatoren sind syntaktischer Sugar für die Funktion JSON_QUERY. Der Ausdruck cart.name entspricht beispielsweise JSON_QUERY(cart, "$.name").

Wenn ein Mitglied mit dem angegebenen Namen nicht im JSON-Objekt gefunden wird oder das JSON-Array kein Element mit der angegebenen Position enthält, geben diese Operatoren SQL NULL zurück.

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

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

Die Gleichheits- und Vergleichsoperatoren werden nicht zum Datentyp JSON definiert. Daher können Sie JSON-Werte nicht direkt in Klauseln wie GROUP BY oder ORDER BY verwenden. Verwenden Sie stattdessen die Funktion JSON_VALUE, um Feldwerte als SQL-Strings zu extrahieren, wie im nächsten Abschnitt beschrieben.

Werte als Strings extrahieren

Die Funktion JSON_VALUE extrahiert einen skalaren Wert und gibt ihn als SQL-String zurück. Gibt SQL NULL zurück, wenn cart.name nicht auf einen skalaren Wert in der JSON-Datei verweist.

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

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

Sie können die JSON_VALUE-Funktion in Kontexten verwenden, die Übereinstimmungen oder Vergleiche erfordern, z. B. WHERE-Klauseln und GROUP BY-Klauseln. Das folgende Beispiel zeigt eine WHERE-Klausel, die nach einem JSON-Wert filtert:

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

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

Alternativ können Sie die Funktion STRING verwenden, die einen JSON-String extrahiert und diesen Wert als SQL-STRING zurückgibt. Beispiel:

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

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

Neben STRING müssen Sie möglicherweise JSON-Werte extrahieren und als weiteren SQL-Datentyp zurückgeben. Die folgenden Funktionen zur Wertextraktion sind verfügbar:

Zum Abrufen des Typs des JSON-Werts können Sie die Funktion JSON_TYPE verwenden.

JSON flexibel konvertieren

Mit LAX Conversion-Funktionen können Sie einen JSON-Wert flexibel und fehlerfrei in einen skalaren SQL-Wert konvertieren.

Das folgende Beispiel zeigt die Leistungsfähigkeit dieser Funktionen. LAX_IN64 leitet die Eingabe automatisch ab und verarbeitet sie korrekt. 

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

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

Zusätzlich zu LAX_IN64 können Sie mit den folgenden Funktionen flexibel in andere SQL-Typen in JSON konvertieren:

Arrays aus JSON extrahieren

JSON kann JSON-Arrays enthalten, die nicht direkt einem ARRAY<JSON>-Typ in BigQuery entsprechen. Mit den folgenden Funktionen können Sie eine ARRAY von BigQuery aus JSON extrahieren:

  • JSON_QUERY_ARRAY: Extrahiert ein Array und gibt es als ARRAY<JSON> von JSON zurück.
  • JSON_VALUE_ARRAY: Extrahiert ein Array von skalaren Werten und gibt es als ARRAY<STRING> skalarer Werte zurück.

Im folgenden Beispiel werden JSON-Arrays mit JSON_QUERY_ARRAY extrahiert.

SELECT JSON_QUERY_ARRAY(cart.items) AS items
FROM mydataset.table1;

+----------------------------------------------------------------+
| items                                                          |
+----------------------------------------------------------------+
| [{"price":10,"product":"book"}","{"price":5,"product":"food"}] |
| [{"price":20,"product":"pen"}]                                 |
+----------------------------------------------------------------+

Verwenden Sie den Operator UNNEST, um ein Array in einzelne Elemente zu unterteilen. Dieser gibt eine Tabelle mit einer Zeile für jedes Element im Array zurück. Im folgenden Beispiel wird das Mitglied product von jedem Mitglied des Arrays items ausgewählt:

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

Das nächste Beispiel ist ähnlich, nutzt jedoch die Funktion ARRAY_AGG, um die Werte wieder in einem SQL-Array zusammenzufassen.

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

Weitere Informationen zu Arrays finden Sie unter Mit Arrays in GoogleSQL arbeiten.

JSON-Nullen

Der Typ JSON hat einen speziellen Wert für null, der sich vom SQL-NULL unterscheidet. Ein JSON-null wird nicht als SQL-NULL-Wert behandelt, wie im folgenden Beispiel gezeigt.

SELECT JSON 'null' IS NULL;

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

Wenn Sie ein JSON-Feld mit einem null-Wert extrahieren, hängt das Verhalten von der Funktion ab:

  • Die Funktion JSON_QUERY gibt einen JSON-null zurück, da es sich um einen gültigen JSON-Wert handelt.
  • Die Funktion JSON_VALUE gibt den SQL-NULL zurück, da JSON null kein skalarer Wert ist.

Das folgende Beispiel zeigt die verschiedenen Verhaltensweisen:

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