BigQuery中如何从数组B中移除数组A包含的元素？

在BigQuery中可以直接使用array_except函数实现这个需求，该函数返回第二个数组中不存在于第一个数组的元素（注意：结果会自动去重）。

基础实现方法 #

假设你的原查询如下：

select 
       array_agg(col1) as A,
       array_agg(col2) as B
from
       your_table

只需在外层嵌套查询，用array_except处理数组B即可：

SELECT
  A,
  ARRAY_EXCEPT(B, A) AS B
FROM (
  -- 替换为你的原查询
  select 
         array_agg(col1) as A,
         array_agg(col2) as B
  from
         your_table
)

针对你提供的示例数据：

原查询结果：

A: [1, 2, 3, 4],
B: [1, 2, 3, 5]

处理后结果：

A: [1, 2, 3, 4],
B: [5]

完全符合期望。

保留重复元素的场景 #

如果数组B中存在重复元素，且需要保留这些不在A中的重复项（array_except会自动去重），可以使用UNNEST结合ARRAY_AGG的方式：

SELECT
  A,
  ARRAY_AGG(b_element) AS B
FROM (
  -- 替换为你的原查询
  select 
         array_agg(col1) as A,
         array_agg(col2) as B
  from
         your_table
),
UNNEST(B) AS b_element
WHERE b_element NOT IN UNNEST(A)
GROUP BY A

例如原B为[1,5,5,3]、A为[1,2,3,4]时，该方法会返回B为[5,5]，而array_except会返回[5]。

内容的提问来源于stack exchange，提问作者RamPrakash