BigQuery で array にデータを追加

BigQuery で array にデータを追加する方法のメモ。

■テーブル定義

create table test1.array_test1 (
  id        string,
  features  array<struct<key string, value string>>
);

■データ登録

insert into test1.array_test1 (id, features) values ('id_01', [struct('key_01' as key, 'value_01' as value)]);

■データ更新（array にデータを追加）

update
  test1.array_test1
set
  features = array_concat(features, [struct('new_key_01' as key, 'new_value_01' as value)])
where
  id = 'id_01'
;

■テーブル登録内容確認

id_01    key_01        value_01
         new_key_01    new_value_01

■データ登録

insert into test1.array_test1 (id) values ('id_02');
■データ更新
select ndores = array_concat(ifnull(features, []), [struct('new_key_02' as key, 'new_value_02' as value)])
where
  id = 'id_02'
;


■テーブル登録内容

id_01    key_01        value_01
         new_key_02    new_value_01
id_02    new_key_02    new_value_02