BigQueryのTO_JSON_STRING関数を用いてSTRUCT型のデータをJSONで扱う
2025.04.01概要
BigQueryではJSON型をテーブル定義で指定することができますが、parquetファイルではJSON型は扱うことができません。
parquetファイルではSTRUCT型として扱って、BigQueryではJSON型としたい時があったとしたら(ないかもしれませんが)、
そんな時はTO_JSON_STRING関数を用いるとSTRUCT型のデータをJSONに変換することができます。
TO_JSON_STRING関数は、BigQueryでSTRUCT、ARRAY、JSON型などの値をJSON文字列（STRING型)に変換する関数です。
それでは使ってみましょう。
使ってみる
parquetファイルと外部テーブルの準備
parquetファイルをソースとする外部テーブルを作成します。parquetファイルのデータは以下です。
| カラム名 | データ型（PyArrow） | BigQueryでの型 |
|---|---|---|
id |
int64 |
INT64 |
user |
struct<first_name: string, last_name: string> |
RECORD（STRUCT） |
| id | user.first_name | user.last_name |
|---|---|---|
| 1 | Alice | Smith |
| 2 | Bob | Johnson |
| 3 | Charlie | Brown |
# parquetファイルをソースとする外部テーブル
CREATE OR REPLACE EXTERNAL TABLE `プロジェクトID.データセット名.struct_example`
OPTIONS (
format = 'PARQUET',
uris = ['gs://バケット名/struct_example.parquet']
);
クエリしてみます。
STRUCT型で持っていることが確認できました。。
JSONで受け取るテーブルを準備
続いて外部テーブルのSTRUCT型データをJSONにして受け取るためのテーブルも作成します。
# 外部テーブルのデータをJSONで受け取るテーブル
CREATE OR REPLACE TABLE `プロジェクトID.データセット名.struct_as_json`
(
id INT64,
user_json JSON
);
これで準備完了です。
TO_JSON_STRINGで変換してみる
外部テーブルからJSONにしてマージしてみます。
# 外部テーブルのデータをJSONに変換してマージするクエリ
MERGE `プロジェクトID.データセット名.struct_as_json` AS target
USING (
SELECT
id,
PARSE_JSON(TO_JSON_STRING(user)) AS user_json
FROM
`プロジェクトID.データセット名.struct_example`
) AS source
ON target.id = source.id
WHEN MATCHED THEN
UPDATE SET
user_json = source.user_json
WHEN NOT MATCHED THEN
INSERT (id, user_json)
VALUES (source.id, source.user_json);
TO_JSON_STRINGはSTRING型で返却するため、JSON型でテーブル定義を作成しているのでPARSE_JSONでJSON型に変換します。
見事にJSON型に変換されているのがわかりますね。
STRUCT型 -> JSON型変換として持ってておきたい方法だなと思います。
参考
