如何在BigQuery的Record列中筛选多组指定键值对？

问题根源 #

你之前的查询无结果的核心原因：UNNEST(labels)会将每个Record数组拆分为单独行，每行仅包含一组key-value对。同一行不可能同时满足label.key='a'和label.key='b'，因此筛选不到匹配数据。

解决方案 #

方案1：用EXISTS子查询直接筛选原表记录

适合仅需保留原始完整记录，同时验证数组包含指定键值对的场景：

WITH data_sept AS (
  SELECT * FROM `k.l.m`
  WHERE invoice.month = '202309'
)
SELECT * FROM data_sept
WHERE 
  EXISTS(SELECT 1 FROM UNNEST(labels) l WHERE l.key = 'a' AND l.value = 'x')
  AND EXISTS(SELECT 1 FROM UNNEST(labels) l WHERE l.key = 'b' AND l.value = 'y')

方案2：用ARRAY_CONTAINS函数简化判断

利用BigQuery内置的ARRAY_CONTAINS直接检查数组是否包含指定STRUCT：

WITH data_sept AS (
  SELECT * FROM `k.l.m`
  WHERE invoice.month = '202309'
)
SELECT * FROM data_sept
WHERE
  ARRAY_CONTAINS(labels, STRUCT('a' AS key, 'x' AS value))
  AND ARRAY_CONTAINS(labels, STRUCT('b' AS key, 'y' AS value))

方案3：分组场景下的筛选适配

如果需要对数据分组，同时确保每组包含所有指定键值对，可先筛选目标标签再聚合验证：

WITH data_sept AS (
  SELECT * FROM `k.l.m`
  WHERE invoice.month = '202309'
),
filtered_labels AS (
  SELECT 
    -- 替换为你需要分组的核心字段（如记录ID、发票编号等）
    id,
    invoice.month,
    label.key,
    label.value
  FROM data_sept
  CROSS JOIN UNNEST(labels) AS label
  -- 先过滤出目标键值对
  WHERE (label.key, label.value) IN (('a','x'), ('b','y'))
)
SELECT 
  id,
  invoice.month,
  -- 根据需求用聚合函数处理其他字段，如ANY_VALUE取任意值、SUM做统计等
  ANY_VALUE(original_column) AS original_column
FROM filtered_labels
GROUP BY id, invoice.month
-- 验证分组后包含所有目标键值对
HAVING COUNT(DISTINCT CONCAT(key, ':', value)) = 2

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