BigQuery でテーブル内の全配列の加算

BigQuery でテーブル内の全レコードの配列を加算する方法のメモ。
■テーブル定義

create table dataset.vector_test (
  id      string,
  vector  array<integer>
);

■データ登録

insert into
  dataset.vector_test
  (id, vector)
values
  ('id1', [0, 1, 2, 3, 4]),
  ('id2', [1, 2, 3, 4, 5]),
  ('id3', [2, 3, 4, 5, 6]);

■加算クエリ

select
array(
  select 
    sum(e)
  from
    dataset.vector_test
    , unnest(vector) as e with offset idx
  group by
    idx
) as sum_vector
;

■結果

[{
  "sum_vector": ["3", "6", "9", "12", "15"]
}]

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"
 }]