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 型の列を宣言します。

Google Cloud コンソールで [BigQuery] ページに移動します。

[BigQuery] に移動
クエリエディタで次のステートメントを入力します。
```
CREATE TABLE mydataset.table1(
  id INT64,
  cart JSON
);
```
[実行] をクリックします。

クエリの実行方法については、インタラクティブクエリを実行するをご覧ください。

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 データを取り込むことができます。

バッチ読み込みジョブを使用して、次の形式から JSON 列に読み込む。
- CSV
- Avro
- JSON
BigQuery Storage Write API を使用する。
以前の tabledata.insertAll ストリーミング API を使用する。

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.name は JSON_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 BY や ORDER 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       |
+------------+------------+

GoogleSQL での JSON データの操作

概要

制限事項

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

SQL

bq

JSON 値を作成する

JSON 値を作成する

STRING 型を JSON 型に変換する

スキーマ化されたデータを JSON に変換する

SQL 型を JSON 型に変換する

JSON データを取り込む

CSV ファイルから読み込む

改行区切りの JSON ファイルから読み込む

Storage Write API を使用する

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

JSON データのクエリ

JSON として値を抽出する

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

JSON を柔軟に変換する

JSON から配列を抽出する

JSON の null

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

`JSON` 値を作成する

`JSON` 値を作成する

`STRING` 型を `JSON` 型に変換する

SQL 型を `JSON` 型に変換する