如何在BigQuery中使用AND条件筛选REPEATED类型字段的重复值

你原有查询的问题在于：将REPEATED类型的columnB拆分为单行后用IN筛选，只要行元素满足任意一个指定值就会被保留，只能实现“或”逻辑，无法判断原数组是否同时包含两个目标值。

方案1：直接数组判断（性能最优） #

不需要展开数组，直接通过IN UNNEST语法判断两个目标值是否均存在于数组中，适合仅需返回columnA的场景：

SELECT columnA
FROM `your_project.your_dataset.your_table`
WHERE 11 IN UNNEST(columnB)
  AND 22 IN UNNEST(columnB)

方案2：聚合校验（适合需要返回原数组或做额外计算的场景） #

如果需要同时返回符合条件的完整columnB数组，可以通过分组后校验匹配元素的去重数量实现：

SELECT columnA, ANY_VALUE(columnB) AS columnB
FROM `your_project.your_dataset.your_table`
CROSS JOIN UNNEST(columnB) AS b
WHERE b IN (11, 22)
GROUP BY columnA
-- 匹配到的去重元素数量等于目标值数量，说明两个值都存在
HAVING COUNT(DISTINCT b) = 2

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