BigQuery如何按数组格式键值对的条件筛选目标数据

前提说明 #

你的原始表结构符合以下特征：

• 包含主键id字段
• 包含存储键值对的结构体数组字段（下文示例用kv_array代指，使用时替换为你表中实际的字段名即可），单个数组元素结构为STRUCT<key STRING, value STRING>，单条元素示例：{key: "abc", value: "true"}
• 筛选要求：返回同时满足abc对应值为false、def对应值为true的原始记录，不需要UNNEST展开后的冗余行。

实现方案 #

方案1：EXISTS 逐行判断（性能最优，推荐大表使用） #

不需要提前全量展开数组，直接在判断条件里用UNNEST遍历数组检查是否存在符合要求的键值对：

SELECT
  id
  -- 如果需要返回kv_array原字段或者其他字段，直接在这里加即可
FROM
  `你的项目ID.你的数据集名.你的表名`
WHERE
  -- 校验存在abc=false的键值对
  EXISTS(
    SELECT 1 
    FROM UNNEST(kv_array) kv 
    WHERE kv.key = 'abc' AND kv.value = 'false'
  )
  AND
  -- 校验存在def=true的键值对
  EXISTS(
    SELECT 1 
    FROM UNNEST(kv_array) kv 
    WHERE kv.key = 'def' AND kv.value = 'true'
  )

方案2：UNNEST展开后聚合校验（适配你已有的展开操作逻辑） #

如果你已经在写UNNEST展开的逻辑，可以展开后按id分组做条件聚合校验：

SELECT
  id
FROM
  `你的项目ID.你的数据集名.你的表名`,
  UNNEST(kv_array) kv
GROUP BY
  id
HAVING
  -- 分组内存在abc=false的记录
  MAX(IF(kv.key = 'abc' AND kv.value = 'false', 1, 0)) = 1
  AND
  -- 分组内存在def=true的记录
  MAX(IF(kv.key = 'def' AND kv.value = 'true', 1, 0)) = 1

注意事项 #

如果你的value字段是实际的BOOL类型而非字符串类型，把判断条件里的'false'、'true'的单引号去掉，直接写布尔值false、true即可，避免触发类型不匹配报错。
如果后续需要新增筛选条件，比如追加xyz=123的要求，直接在WHERE（方案1）或HAVING（方案2）中追加对应判断逻辑即可。

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