【新機能】BigQuery で JSON オブジェクトのキーの一覧を取得できるようになりました

hanzawa.yuya

2024.08.09

Google Cloudデータエンジニアのはんざわです。
2024年8月8日のアップデートで JSON_KEYS が新たにプレビューで追加されました。

本ブログでは、さっそくその使い方を見ていきましょう。

JSON_KEYS とは

JSON_KEYS は、JSON functionsの一種で JSON オブジェクトからキーの一覧を取得できる関数です。
これまでは JavaScript の UDF で Object.keys を使うなどして取得していましたが、このアップデートにより BigQuery のネイティブ関数として利用できるようになったため、よりシンプルに記述できるようになりました。

今回のアップデートは一見地味ですが、個人的には待望の機能追加でした！

実際に触ってみる

早速、試してみましょう！
使い方は簡単で、JSON_KEYS で JSON 型のデータを渡すだけでキーの一覧を取得できます。

シンプルなオブジェクト

まずは、シンプルな JSON オブジェクトを作成し、キーの一覧を取得してみましょう。
公式ドキュメントによると、キーの重複は取り除かれ、アルファベット順に返されるとのことです。

WITH sample_table AS (
    SELECT JSON """{
        "userId": 101,
        "username": "john",
        "country": "Japan"
      }""" AS json_col
)

SELECT
  JSON_KEYS(json_col) AS json_keys
FROM
  sample_table

> 
/*-----------*
 | json_keys |
 *-----------*
 | country   |
 | userId    |
 | username  |
 *-----------*/

ネストされたオブジェクト

次に、ネストされた JSON オブジェクトで試してみましょう。以下の例のように、ネストされたフィールドにも問題なくアクセスできます。
また、JSON_KEYS(json_col, 1) のように max_depth を指定することで、ネストされたフィールドの深さを制限することも可能です。

WITH sample_table AS (
    SELECT JSON """{
        "userId": 101,
        "username": "john",
        "country": "Japan",
        "order":
          {
            "orderId": 5001,
            "product": "Laptop",
            "price": 120000
          }
      }""" AS json_col
)

SELECT
  JSON_KEYS(json_col) AS json_keys
FROM
  sample_table

> 
/*---------------*
 |   json_keys   |
 *---------------*
 | country       |
 | order         |
 | order.orderId |
 | order.price   |
 | order.product |
 | userId        |
 | username      |
 *---------------*/

配列を含むネストされたオブジェクト

最後に、配列を含むネストされた JSON オブジェクトで試してみましょう。
以下の例のように orders の値は、要素が JSON オブジェクトである配列です。
しかし、結果を見ると分かるように、配列の中のオブジェクトのキーは取得できていません。

WITH sample_table AS (
    SELECT JSON """{
        "userId": 101,
        "username": "john",
        "country": "Japan",
        "orders": [
            {
              "orderId": 5001,
              "product": "Laptop",
              "quantity": 1,
              "price": 120000
            },
            {
              "orderId": 5002,
              "product": "Mouse",
              "quantity": 2,
              "price": 2500
            }
          ]
      }""" AS json_col
)

SELECT
  JSON_KEYS(json_col) AS json_keys
FROM 
  sample_table

> 
/*-----------*
 | json_keys |
 *-----------*
 | country   |
 | orders    |
 | userId    |
 | username  |
 *-----------*/

配列の中の JSON オブジェクトにアクセスするには、mode => "lax" のオプジョンを指定する必要があるようです。以下のクエリはその例になります。

WITH sample_table AS (
    SELECT JSON """{
        "userId": 101,
        "username": "john",
        "country": "Japan",
        "orders": [
            {
              "orderId": 5001,
              "product": "Laptop",
              "price": 120000
            },
            {
              "orderId": 5002,
              "product": "Mouse",
              "price": 2500
            }
          ]
      }""" AS json_col
)

SELECT
  JSON_KEYS(json_col, mode => "lax") AS json_keys
FROM 
  sample_table

>
/*----------------*
 |   json_keys    |
 *----------------*
 | country        |
 | orders         |
 | orders.orderId |
 | orders.price   |
 | orders.product |
 | userId         |
 | username       |
 *----------------*/

mode => "lax" のオプションを指定することで、配列の中の JSON オブジェクトのキーも取得できるようになりました。
このようなオプションが事前に用意されているのは非常にありがたいですね。

まとめ

本ブログでは、BigQuery に新たに追加された JSON_KEYS 関数を紹介しました。
個人的には、UDF を使わずに BigQuery ネイティブで JSON のキーを取得できるようになるのを待ち望んでいたので、今回のアップデートは非常に嬉しいです。
プレビュー段階ではありますが、正式リリースが待ち遠しいですね！