【新機能】BigQueryでJSON型が使えます！（BigQuery subscriptionsも対応）

データアナリティクス事業本部、池田です。
BigQueryのJSON型は申請が必要な プレビュー という認識だったのですが、いつの間にか普通に使えるようになっていました！ （某所にも確認しましたが、GA（一般提供）という扱いで良いそうです。 以下の公式ガイドにもプレビューの記述はありません！）

【 Working with JSON data in Google Standard SQL 】

Amazon Redshift だと SUPER タイプ 、 Snowflake だと VARIANT などが近いかと思います。

STRUCT型 に比べて、スキーマオンリード的に、より柔軟に値を格納できます。

使ってみる

軽く使ってみます。
JSONリテラル は以下のような構文です。
JSON ＜JSONの文字列＞

リファレンスの例を元に、簡単なSELECT文を動かしてみます。

-- CREATE TABLE my_json.verify AS
WITH sample_table AS (
    SELECT JSON """{
    "id": 10,
    "type": "fruit",
    "name": "apple",
    "on_menu": true,
    "recipes":
        {
            "salads":
            [
                { "id": 2001, "type": "Walnut Apple Salad" },
                { "id": 2002, "type": "Apple Spinach Salad" }
            ],
            "desserts":
            [
                { "id": 3001, "type": "Apple Pie" },
                { "id": 3002, "type": "Apple Scones" },
                { "id": 3003, "type": "Apple Crumble" }
            ]
        }
    }""" AS json_col
)
SELECT json_col, json_col.id, json_col.recipes.salads FROM sample_table;

ドットで簡単に各要素へアクセスできていますね。

ちなみに、前述のSELECT文をCTASでテーブルにしてみると…

全てJSON型になっていました。
適切に扱うには適宜キャストが必要そうです。

…
SELECT json_col, INT64(json_col.id), json_col.recipes.salads FROM sample_table;

nullが特殊

冒頭の公式ガイド で気になった記述が以下です。

The JSON type has a special null value that is different from the SQL NULL. A JSON null is not treated as a SQL NULL value, …

SELECT
    JSON 'null' IS NULL AS a,
    JSON_VALUE(JSON 'null') IS NULL AS b;

-- a	b
-- false	true

null の扱いには注意が必要そうです。

BigQuery subscriptions

以前ブログにしたBigQuery subscriptions（ Cloud Pub/Sub からBigQueryへ直接データを投入する機能）でもメッセージをJSON型として格納できるようになりました。
【 Pub/Sub release notes > November 15, 2022 】

簡単に動かしてみます。 詳細は以前のブログをご覧下さい。

BigQuery subscriptionsを使ってPub/Subと直接連携する

以前は（ トピック スキーマ を使わずに）メッセージを受け取るのは data というSTRING型のカラムである必要がありました。

CREATE TABLE subscriptions.sample_with_meta (
    subscription_name STRING,
    message_id STRING,
    publish_time TIMESTAMP,
    data STRING,
    attributes STRING
)
PARTITION BY DATE(publish_time);

↓↓↓↓↓↓↓↓

JSON型で受け取れます！

CREATE TABLE subscriptions.sample_with_meta_json (
    subscription_name STRING,
    message_id STRING,
    publish_time TIMESTAMP,
    data JSON,
    attributes STRING
)
PARTITION BY DATE(publish_time);

※↑では直し忘れたのですが、 attributes カラムもJSON型で受け取れます。

一般に公開されているPub/Subのトピック をサブスクライブ登録します。

gcloud pubsub subscriptions create sample-with-meta-json-sub \
--topic=projects/pubsub-public-data/topics/taxirides-realtime \
--bigquery-table=my-project.subscriptions.sample_with_meta_json \
--write-metadata

データが格納されます。（↓画像は左が以前のもの、右が今回のもの。）

見た目ではSTRING型と区別がつかないです…

要素へのアクセス

以前は以下のように要素にアクセスして、ビューにしていました。

STRING型のdataカラム

CREATE VIEW subscriptions.sample_view AS
    SELECT
        JSON_VALUE(data, '$.ride_id') AS ride_id,
        SAFE_CAST(JSON_VALUE(data, '$.point_idx') AS INT64) AS point_idx,
        SAFE_CAST(JSON_VALUE(data, '$.latitude') AS BIGNUMERIC) AS latitude,
        SAFE_CAST(JSON_VALUE(data, '$.longitude') AS BIGNUMERIC) AS longitude,
        SAFE_CAST(JSON_VALUE(data, '$.timestamp') AS TIMESTAMP) AS timestamp,
        SAFE_CAST(JSON_VALUE(data, '$.meter_reading') AS NUMERIC) AS meter_reading,
        SAFE_CAST(JSON_VALUE(data, '$.meter_increment') AS NUMERIC) AS meter_increment,
        JSON_VALUE(data, '$.ride_status') AS ride_status,
        SAFE_CAST(JSON_VALUE(data, '$.passenger_count') AS INT64) AS passenger_count
    FROM
        subscriptions.sample_with_meta
    WHERE
        DATE(publish_time) >= CURRENT_DATE()
;

※ JSON_VALUE() はSTRING型もJSON型も受け取れるので、実はこのアクセス方法もそのまま使えます。

↓↓↓↓↓↓↓↓

JSON型のdataカラム

CREATE VIEW subscriptions.sample_view_json AS
    SELECT
        data.ride_id,
        data.point_idx,
        data.latitude,
        data.longitude,
        data.timestamp,
        data.meter_reading,
        data.meter_increment,
        data.ride_status,
        data.passenger_count
    FROM
        subscriptions.sample_with_meta_json
    WHERE
        DATE(publish_time) >= CURRENT_DATE()
;

だいぶシンプルに要素にアクセスできるようになりました。 （分かりやすくするためキャストは省略しました。）

おわりに

ずっと待っていました。

関連情報/参考にさせていただいたページ

• Working with JSON data in Google Standard SQL
• JSON type
• JSON literals
• JSON 関数
• BigQuery subscriptions
• BigQuery subscriptionsを使ってPub/Subと直接連携する
• プレビューに関する記事
  • BigQueryがJSONにネイティブ対応。SQLでJSONに対するクエリが可能に
  • BigQueryのネイティブJSON型がサポートされたことでどう変わったのか