在 GoogleSQL 中使用 JSON 資料

本文說明如何建立含有 JSON 欄的資料表、將 JSON 資料插入 BigQuery 資料表，以及查詢 JSON 資料。

BigQuery 原生支援使用 JSON 資料類型的 JSON 資料。

JSON 是一種廣為使用的格式，可處理半結構化資料，因為不需要結構定義。應用程式可採用「讀取時結構定義」方法，也就是應用程式會擷取資料，然後根據對該資料結構定義的假設進行查詢。這個方法與 BigQuery 中的 STRUCT 類型不同，後者需要固定結構定義，且會對儲存在 STRUCT 類型資料欄中的所有值強制執行。

使用 JSON 資料類型，您就能將半結構化 JSON 載入 BigQuery，不必預先提供 JSON 資料的結構定義。您可以儲存及查詢不一定符合固定結構定義和資料類型的資料。將 JSON 資料擷取為 JSON 資料類型後，BigQuery 就能個別編碼及處理每個 JSON 欄位。然後，您可以使用欄位存取運算子查詢 JSON 資料中的欄位和陣列元素值，讓 JSON 查詢直覺易懂且經濟實惠。

限制

  • 如果您使用批次載入作業將 JSON 資料擷取至資料表，來源資料必須為 CSV、Avro 或 JSON 格式。系統不支援其他批次載入格式。
  • JSON 資料類型的巢狀結構上限為 500。
  • 您無法使用舊版 SQL 查詢含有 JSON 型別的資料表。
  • 資料列層級存取政策無法套用至 JSON 資料欄。

如要瞭解 JSON 資料類型的屬性，請參閱 JSON 類型。

建立含有 JSON 資料欄的資料表

您可以使用 SQL 或 bq 指令列工具，建立含有 JSON 資料欄的空白資料表。

SQL

使用 CREATE TABLE 陳述式，並宣告 JSON 型別的資料欄。

  1. 前往 Google Cloud 控制台的「BigQuery」頁面。

    前往「BigQuery」

  2. 在查詢編輯器中輸入下列陳述式：

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

  3. 按一下「執行」。

如要進一步瞭解如何執行查詢，請參閱「執行互動式查詢」。

bq

使用 bq mk 指令，並提供具有 JSON 資料類型的資料表結構定義。

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

您無法根據 JSON 資料欄對資料表分區或叢集，因為等號和比較運算子未在 JSON 型別中定義。

建立 JSON 值

您可以透過下列方式建立 JSON 值：

建立JSON值

以下範例會將 JSON 值插入資料表：

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 型別轉換為 JSON 型別

下列範例使用 PARSE_JSON 函式，轉換 JSON 格式的 STRING 值。這個範例會將現有資料表的資料欄轉換為 JSON 型別，並將結果儲存到新資料表。

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

本範例中使用的 SAFE 前置字元可確保所有轉換錯誤都會以 NULL 值傳回。

將結構定義資料轉換為 JSON

以下範例使用 JSON_OBJECT 函式，將鍵/值組合轉換為 JSON。

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

結果如下：

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

將 SQL 型別轉換為 JSON 型別

以下範例使用 TO_JSON 函式，將 SQL STRUCT 值轉換為 JSON 值：

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

結果如下：

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

擷取 JSON 資料

您可以透過下列方式將 JSON 資料擷取至 BigQuery 資料表：

從 CSV 檔案載入

以下範例假設您有名為 file1.csv 的 CSV 檔案，其中包含下列記錄：

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

請注意，第二欄包含以字串編碼的 JSON 資料。這包括正確逸出 CSV 格式的引號。在 CSV 格式中，引號會使用 "" 雙字元序列逸出。

如要使用 bq 指令列工具載入這個檔案，請使用 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

從以換行符號分隔的 JSON 檔案載入

以下範例假設您有名為 file1.jsonl 的檔案，其中包含下列記錄：

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

如要使用 bq 指令列工具載入這個檔案，請使用 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 (gRPC)

您可以使用 Storage Write API (gRPC) 擷取 JSON 資料。以下範例使用 Storage Write API (gRPC) Python 用戶端，將資料寫入含有 JSON 資料類型資料欄的資料表。

定義通訊協定緩衝區，用來保存序列化的串流資料。JSON 資料會編碼為字串。在下列範例中，json_col 欄位會保留 JSON 資料。

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

將每一列的 JSON 資料格式設為 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'

如程式碼範例所示，將資料列附加至寫入串流。用戶端程式庫會處理序列化作業，將資料轉換為通訊協定緩衝區格式。

如果無法格式化傳入的 JSON 資料，您需要在程式碼中使用 json.dumps() 方法。範例如下：

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)

...

使用 Storage Write API (REST)

以下範例會從本機檔案載入 JSON 資料，並使用 Storage Write API (REST) 將資料串流至 BigQuery 資料表，該資料表含有名為 json_data 的 JSON 資料型別資料欄。

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": {