google-bigquery - 大查询 : Concatenate two arrays and keep distinct values within MERGE statement

Question

我正在处理 MERGE 过程并使用新数据更新数组字段但前提是尚未在数组中找到该值。

target table
+-----+----------+
| id  |  arr_col |
+-----+----------+
| a   |  [1,2,3] |
| b   |    [0]   |
+-----+----------+

source table
+-----+----------+
| id  |  arr_col |
+-----+----------+
| a   | [3,4,5] |
| b   |  [0,0]   |
+-----+----------+

target table post-merge
+-----+-------------+
| id  |  arr_col    |
+-----+-------------+
| a   | [1,2,3,4,5] |
| b   |    [0]      |
+-----+-------------+

我正在尝试使用 SQL on this answer在我的 MERGE 语句中

merge into target t
using source
  on target.id = source.id
when matched then
update set target.arr_col = array(
                             select distinct x 
                             from unnest(array_concat(target.arr_col, source.arr_col)) x
                            ) 

但 BigQuery 向我显示以下错误:相关子查询在 UPDATE 子句中不受支持。

有没有其他方法可以通过 MERGE 更新这个数组字段？目标表和源表可能非常大并且会每天运行。所以我希望这是一个增量更新的过程，而不是每次都用新数据重新创建整个表。

Answer 1

以下是 BigQuery 标准 SQL

merge into target
using (
  select id, 
    array(
      select distinct x 
      from unnest(source.arr_col || target.arr_col) as x
      order by x
    ) as arr_col
  from source 
  join target
  using(id)
) source
  on target.id = source.id
when matched then
update set target.arr_col = source.arr_col;