【アップデート情報】BigQueryでJson functionsが新たに追加されました

Google Cloudデータエンジニアのはんざわです。
2023年8月7日のアップデートでいくつかのJson functionsが新たにGAになったので紹介します。

release-notes August_07_2023

新たに追加されたJson functions

今回のアップデートで合計で11個のJson functionsが新たにGAになりました。

1. JSON_ARRAY：JSON_ARRAYの公式ドキュメント

2. JSON_ARRAY_APPEND：JSON_ARRAY_APPENDの公式ドキュメント

3. JSON_ARRAY_INSERT：JSON_ARRAY_INSERTの公式ドキュメント

4. JSON_OBJECT：JSON_OBJECTの公式ドキュメント

5. JSON_REMOVE：JSON_REMOVEの公式ドキュメント

6. JSON_SET：JSON_SETの公式ドキュメント

7. JSON_STRIP_NULLS：JSON_STRIP_NULLSの公式ドキュメント

8. LAX_BOOL：LAX_BOOLの公式ドキュメント

9. LAX_FLOAT64：LAX_FLOAT64の公式ドキュメント

10. LAX_INT64：LAX_INT64の公式ドキュメント

11. LAX_STRING：LAX_STRINGの公式ドキュメント

それぞれの関数の詳細は上記の公式ドキュメントを参照してみてください。
例も豊富で非常に見やすかったです。

また、公式のブログもあるので併せて参考にしてみてください。

今回の記事ではこの中でも自分が特に注目しているJSON_OBJECTとJSON_REMOVEを紹介したいと思います。

JSON_OBJECT

JSON_OBJECTではKeyとValueを渡すことでJsonオブジェクトを作成することができます。
以下の例では、fooに10を渡し、barにTRUEを渡しています。

SELECT JSON_OBJECT('foo', 10, 'bar', TRUE) AS json_data

/*-----------------------*
 | json_data             |
 +-----------------------+
 | {"bar":true,"foo":10} |
 *-----------------------*/

これの何が嬉しいかというと既存のデータ基盤を丸ごとJsonオブジェクトに変換し、Json型で保持することが可能になりました。

実際に触ってみる

今回の検証ではサンプルデータセットのtrigramsを使用します。

下記の画像はJson型変換前のテーブル情報です。

次に以下のクエリで対象のテーブルをJson型で丸ごと保持するようにします。

CREATE OR REPLACE TABLE test.json_object_table AS
SELECT
    JSON_OBJECT
    (
      'ngram', ngram,
      'first', first,
      'second', second,
      'third', third,
      'fourth', fourth,
      'fifth', fifth,
      'cell', cell
    ) AS json_data 
FROM
    samples_dataset.trigrams

正常にjson_dataカラムのJson型として丸ごと保持することができました。
次にテーブル情報を確認してみましょう。

通常のテーブルと比べて、Json型のテーブルは物理バイト数が減っていることがわかると思います。
BigQueryの新料金プランでは、物理ストレージを基準に課金されるため物理ストレージ量を減らすことがコスト削減に繋がると思います。

JSON_REMOVE

JSON_REMOVEはJsonデータから指定したパスやキーのデータを削除することができます。

• Json型のARRAY

SELECT JSON_REMOVE(JSON '["a", "b", "c"]', '$[1]') AS json_data

/*-----------*
 | json_data |
 +-----------+
 | ["a","c"] |
 *-----------*/

/* 削除対象を複数選択することも可能 */
SELECT JSON_REMOVE(JSON '["a", "b", "c"]', '$[0]', '$[0]') AS json_data

/*-----------*
 | json_data |
 +-----------+
 | ["c"]     |
 *-----------*/

• Jsonオブジェクト

SELECT JSON_REMOVE(JSON '{"a": 1, "b": {"c": "2"}}', '$.a') AS json_data

/*-----------------*
 | json_data       |
 +-----------------+
 | {"b":{"c":"2"}} |
 *-----------------*/

/* 階層化されたJsonもアクセス可能 */
SELECT JSON_REMOVE(JSON '{"a": 1, "b": {"c": "2"}}', '$.b.c') AS json_data

/*----------------*
 | json_data      |
 +----------------+
 | {"a":1,"b":{}} |
 *----------------*/

弊チームでは既にUDFでJson型から要素を削除する関数を作成し、使用していたのでそれが正式にサポートされるようになった感じです。

JSON_OBJECTの項目で紹介したように要素を丸ごとJson型データで保持するような場合、どうしても重複確認や結合キーの兼ね合いでそれに該当するカラムは事前に摘出する必要があります。
その際に摘出したカラムをJson型データから削除するとさらにストレージ効率が高くなると思います。
また、特定のカラムに機密情報などが含まれる場合も削除することが望ましいと思われます。

まとめ

新たに追加されたJson functionsのうち、JSON_OBJECTとJSON_REMOVEを紹介しました。
今回紹介できなかった関数はまだまだたくさんありますので是非公式ドキュメントを確認し、有効に活用してみてください。