BigQueryのTO_JSON_STRING関数を用いてSTRUCT型のデータをJSONで扱う

#Google Cloud (GCP)

#Google BigQuery

根本夏瑠

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型)に変換する関数です。

https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#to_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型変換として持ってておきたい方法だなと思います。
 参考https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#to_json_string