GoogleSQL での JSON データの操作

BigQuery は、JSON データ型を使用する JSON データをネイティブにサポートしています。

このドキュメントでは、JSON 列を含むテーブルの作成、BigQuery テーブルへの JSON データの挿入、JSON データのクエリを実行する方法について説明します。

概要

JSON は、スキーマを必要としないため、半構造化データを可能にする形式として広く使用されています。アプリケーションは「スキーマオンリード」アプローチを使用できます。このアプローチでは、アプリケーションはデータを取り込んでから、そのデータのスキーマに関する前提条件に基づいてクエリを実行します。このアプローチは、STRUCT 型の列に格納されているすべての値が適用される固定スキーマが必要な BigQuery の STRUCT 型とは異なります。

JSON データ型を使用すると、JSON データのスキーマを事前に指定しなくても、半構造化 JSON を BigQuery に読み込むことができます。これにより、固定のスキーマやデータ型に必ずしも従わないデータの格納や、クエリの実行ができます。JSON データを JSON データ型として取り込むと、BigQuery で各 JSON フィールドを個別にエンコードして処理できます。その後、フィールド アクセス演算子を使用して JSON データ内のフィールドの値と配列要素の値をクエリできます。これにより、JSON クエリが直感的でコスト効率の高いものになります。

制限事項

  • バッチ読み込みジョブを使用して JSON データをテーブルに取り込む場合、ソースデータは CSV、Avro、または JSON 形式である必要があります。その他のバッチ読み込み形式はサポートされていません。
  • JSON データ型のネストの上限は 500 です。
  • レガシー SQL を使用して JSON 型を含むテーブルにクエリを実行することはできません。
  • 行レベルのアクセス ポリシーを JSON 列に適用することはできません。

JSON データ型のプロパティについては、JSONをご覧ください。

JSON 列を含むテーブルを作成する

JSON 列を含む空のテーブルを作成するには、SQL または bq コマンドライン ツールを使用します。

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 値を作成できます。

  • SQL を使用して JSON リテラルを作成します。
  • PARSE_JSON 関数を使用して STRING 値を JSONに変換します。
  • TO_JSON 関数を使用して SQL 値を JSONに変換します。
  • JSON_ARRAY 関数を使用して SQL 値から JSON 配列を作成します。
  • JSON_OBJECT 関数を使用して Key-Value ペアから 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 関数を使用して Key-Value ペアを 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 データを取り込む

次の方法で BigQuery テーブルに JSON データを取り込むことができます。

CSV ファイルから読み込む

次の例では、file1.csv という名前の CSV ファイルに次のレコードが含まれていることを前提としています。

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

2 番目の列には、文字列としてエンコードされた JSON データが含まれています。このため、CSV 形式の引用符を正しくエスケープする必要があります。CSV 形式では、引用符は 2 文字シーケンス "" を使用してエスケープされます。

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 を使用する

Storage Write API を使用して JSON データを取り込むことができます。次の例では、Storage Write API の Python クライアントを使用します。

シリアル化されたストリーミング データを保持するプロトコル バッファを定義します。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'

サンプルコードに示すように、書き込みストリームに行を追加します。クライアント ライブラリにより、プロトコル バッファ形式へのシリアル化が処理されます。

以前のストリーミング API を使用する

次の例では、以前のストリーミング API を使用して、ローカル ファイルから JSON データを読み込み、BigQuery にストリーミングします。

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

詳しくは、BigQuery へのデータのストリーミングをご覧ください。

JSON データのクエリ

このセクションでは、GoogleSQL を使用して JSON から値を抽出する方法について説明します。JSON では大文字と小文字が区別され、フィールドと値の両方で UTF-8 がサポートされています。

このセクションの例では、次のテーブルを使用しています。

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

JSON として値を抽出する

BigQuery の JSON 型の場合、フィールド アクセス演算子を使用することで、JSON 式のフィールドにアクセスできます。次の例は、cart 列の name フィールドを返します。

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

配列要素にアクセスするには、JSON 添字演算子を使用します。次の例は、items 配列の最初の要素を返します。

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

また、JSON 添字演算子を使用して JSON オブジェクトのメンバーを名前で参照することもできます。

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

添字演算の場合、かっこ内の式には、非定数式を含む任意の文字列または整数式を使用できます。

DECLARE int_val INT64 DEFAULT 0;

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

フィールド アクセス演算子と添字演算子はどちらも JSON 型を返すため、それらを使用する式を連結するか、JSON 型を取る他の関数に結果を渡すことができます。

これらの演算子は、JSON_QUERY 関数の糖衣構文です。たとえば、式 cart.nameJSON_QUERY(cart, "$.name") と同等です。

指定された名前のメンバーが JSON オブジェクトで見つからない場合、または JSON 配列に指定した位置の要素がない場合、これらの演算子は SQL NULL を返します。

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

等価演算子と比較演算子は JSON データ型で定義されていません。そのため、GROUP BYORDER BY などの句で JSON 値を直接使用することはできません。代わりに、次のセクションで説明するように、JSON_VALUE 関数を使用してフィールド値を SQL 文字列として抽出します。

文字列として値を抽出する

JSON_VALUE 関数はスカラー値を抽出し、SQL 文字列として返します。cart.name が JSON 内のスカラー値を指していない場合は、SQL NULL を返します。

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

JSON_VALUE 関数は、WHERE 句や GROUP BY 句などの等価や比較を必要とするコンテキストで使用できます。次の例は、JSON 値でフィルタする WHERE 句を示しています。

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

または、JSON 文字列を抽出し、その値を SQL STRING として返す STRING 関数を使用することもできます。次に例を示します。

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

STRING に加えて、JSON 値を抽出して、別の SQL データ型として返す必要がある場合があります。次の値抽出関数を使用できます。

JSON 値の型を取得するには、JSON_TYPE 関数を使用できます。

JSON を柔軟に変換する

LAX Conversion 関数を使用すると、JSON 値をスカラー SQL 値に柔軟にエラーなく変換できます。

次の例は、これらの関数の機能を示しています。LAX_IN64 は、入力を自動的に推測して正しく処理します。

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

LAX_IN64 に加えて、次の関数を使用すると、他の SQL 型に柔軟に JSON に変換できます。

JSON から配列を抽出する

JSON には JSON 配列を含めることができます。これは、BigQuery の ARRAY<JSON> 型と直接同等ではありません。次の関数を使用して、JSON から BigQuery ARRAY を抽出できます。

  • JSON_QUERY_ARRAY: 配列を抽出して JSON の ARRAY<JSON> として返します。
  • JSON_VALUE_ARRAY: スカラー値の配列を抽出し、スカラー値の ARRAY<STRING> として返します。

次の例は、JSON_QUERY_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"}]                                 |
+----------------------------------------------------------------+

配列を個々の要素に分割するには、UNNEST 演算子を使用します。この演算子は、配列内の各要素を 1 行とするテーブルを返します。次の例では、items 配列の各メンバーから product メンバーを選択します。

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

次の例も似ていますが、ARRAY_AGG 関数を使用して値を 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"]         |
+----+-----------------+

配列の詳細については、GoogleSQL での配列の操作をご覧ください。

JSON の null

JSON 型には、SQL の NULL とは異なる特別な null 値があります。次の例に示すように、JSON null は SQL の NULL 値として扱われません。

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

null 値を含む JSON フィールドを抽出する場合、動作は関数によって異なります。

  • 有効な JSON 値であるため、JSON_QUERY 関数は JSON null を返します。
  • JSON の null はスカラー値ではないため、JSON_VALUE 関数は SQL の NULL を返します。

次の例は、さまざまな動作を示しています。

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