BigQuery でデータを JSON 化して配列に集約

BigQuery でデータ項目を struct() で JSON 化して、array_agg() で配列に集約する方法のメモ。

BigTable には以下のデータを登録します。

insert into tbl values ('item_01', 'store_01', 100);
insert into tbl values ('item_01', 'store_02', 110);
insert into tbl values ('item_01', 'store_03', 120);

insert into tbl values ('item_02', 'store_01', 200);
insert into tbl values ('item_02', 'store_02', 210);
insert into tbl values ('item_02', 'store_03', 220);

insert into tbl values ('item_03', 'store_04', 300);

以下のクエリで JSON 化したデータを配列に集約します。
struct(store_cd, price) で {"store_cd": ..., "price": ... } の JSON 形式に変換し、array_agg() で item_id が同じレコードを集約します。

select
   item_id
  , array_agg(store_info)
from (
select
   item_id
  , struct(store_cd, price) as store_info
from
  tbl
)
group by
  item_id
;

実行結果は以下の通りで、同一 item_id で {"store_cd": ..., "price": ...} を集約した配列が得られます。

{"item_id": "item_01", "store_infos": [{ "store_cd": "store_03", "price": "120"}, {"store_cd": "store_01", "price": "100"}, {"store_cd": "store_02", "price": "110"}]}
{"item_id": "item_02", "store_infos": [{"store_cd": "store_02", "price": "210"}, {"store_cd": "store_03", "price": "220"}, { "store_cd": "store_01", "price": "200"}]}
{"item_id": "item_03", "store_infos": [{"store_cd": "store_04", "price": "300"}]}