BigQuery中如何查询含转义引号的JSON字段并提取特定属性？

问题场景 #

我有一个存储大体积JSON对象的BigQuery表，仅需提取每个JSON里content字段嵌套的单个属性。但content的值是带转义引号的JSON字符串（示例："content": "{\"Performance\": null, \"RatabaseXML\": null}"），其他字段无转义问题。BigQuery原生的JSON_EXTRACT/JSON_EXTRACT_SCALAR无法直接处理这种格式，希望找到无需加载整个大JSON对象做全局替换就能提取目标属性的方法。该JSON格式有效，问题仅针对content字段内的转义引号。

解决方案 #

不用全局替换整个大体积JSON，只针对性处理content字段的字符串值即可，步骤如下：

1. 先从外层JSON中提取content的原始字符串值；
2. 修复该字符串的转义格式（替换内部的\"为"，去掉开头多余的"）；
3. 将修复后的字符串解析为JSON，再提取目标属性。

对应的SQL代码：

WITH json_sample AS (
  SELECT
    '{"applicationName": "familycarservice", "content": "{\"Performance\": \"cats\", \"RatabaseXML\": null}"}' AS payload -- 补全示例中缺失的闭合引号
)
SELECT
  JSON_EXTRACT_SCALAR(
    -- 仅处理content字段的内容，而非整个payload
    REPLACE(REPLACE(JSON_EXTRACT_SCALAR(payload, '$.content'), '\\"', '"'), '"{', '{' ),
    '$.Performance'
  ) AS Performance
FROM json_sample;

说明 #

• 这种方式只处理content字段的内容，避免了加载整个大JSON对象做全局替换，性能更优；
• 如果content的转义规则固定（仅存在\"和开头的"{），上述替换逻辑完全适用；若有其他转义场景，可调整REPLACE规则；
• 确保content字段的字符串修复后是有效的JSON，否则解析会失败。

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