BigQuery の array_to_string() で文字列の配列を連結

BigQuery の array_to_string() で文字列の配列を連結する方法のメモ。

スキーマイメージ

jsonPayload
  items      array<object>
    item_id  string
    num      integer

クエリ例

select
  jsonPayload.order_id as order_id
  , array_to_string(
      array(select concat(item_id, ':', num) from unnest(jsonPayload.items) order by item_id asc)
    , ',') as item_num_string
from
  `dataset.table`
;

実行結果例

[{"order_id": "order1",
  "item_num_string": "item1:10,item2:20"
 },
 {"order_id": "order2",
  "item_num_string": "item3:30,item4:40,item5:50"
 }]