如何在BigQuery中过滤数组字段内的指定元素

实现方法 #

你可以直接用BigQuery原生的数组过滤语法ARRAY()结合UNNEST()实现需求，不需要额外自定义函数，写法非常简洁：

-- 示例：基于你提供的样例数据实现过滤
WITH origin_table AS (
  SELECT ["apple", "of", "the", "tree"] AS array_col, 1 AS label
  UNION ALL 
  SELECT ["boy", "of", "the", "streets"] AS array_col, 2 AS label
)
SELECT
  -- 对array_col做元素过滤
  ARRAY(
    SELECT item 
    FROM UNNEST(array_col) AS item
    -- 这里写要排除的元素列表
    WHERE item NOT IN ('of', 'the')
  ) AS array_col,
  label
FROM origin_table

语法说明 #

• UNNEST(array_col)会将数组字段拆分为多行单元素的临时结果
• 子查询基于过滤条件筛选需要保留的元素后，外层ARRAY()函数会自动把筛选后的元素重新拼接为数组
• 后续如果需要调整要剔除的元素，直接修改NOT IN后的列表即可

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