在GCP BigQuery中匹配嵌套JSON字段值并过滤特定状态

你的查询无结果返回，核心问题在于**responseContent是JSON数组而非单个JSON对象**，原查询的JSON路径错误，导致无法正确匹配status值。

原查询的主要问题 #

1. JSON路径错误：示例中responseContent是[{"..."}]格式的数组，直接用$.status无法定位到数组内元素的status字段，必须指定数组索引（如$[0].status）或展开数组。
2. 条件逻辑与需求不符：你需要排除status: MatchFailed的负载，但原查询用了= "MatchPending"，若日志中没有该状态的记录，自然无结果；且即使有，路径错误也会导致匹配失败。
3. 冗余的正则提取：JSON_VALUE已经能直接解析JSON字段，无需用正则处理转义引号。

修正后的查询 #

场景1：responseContent始终是单元素数组

直接通过数组索引定位元素：

SELECT
  JSON_VALUE(json_payload.proxy_details.responseContent, '$[0].status') AS status
FROM
  `prj.global._Default._Default` AS logs
WHERE
  log_name = "projects/prj/logs/logs"
  AND JSON_VALUE(json_payload.proxy_details.responseContent, '$[0].status') != "MatchFailed"

场景2：responseContent可能包含多个元素

用UNNEST展开数组，处理所有元素：

SELECT
  elem.status AS status
FROM
  `prj.global._Default._Default` AS logs,
  UNNEST(JSON_QUERY_ARRAY(json_payload.proxy_details.responseContent)) AS elem
WHERE
  log_name = "projects/prj/logs/logs"
  AND elem.status != "MatchFailed"

额外说明 #

• 如果你的目标是仅获取status: MatchPending的记录，确保日志中存在该状态的负载，同时修正JSON路径即可。
• JSON_QUERY_ARRAY用于将JSON数组字符串转换为BigQuery可处理的数组，UNNEST则将数组拆分为行，方便逐元素筛选。

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