BigQuery解析含双重双引号的嵌套字符串JSON字段返回null如何解决

问题原因 #

• 双重双引号是核心诱因：你存储的字段为转义后的JSON字符串，所有双引号被替换为双重双引号，外层还额外包裹了一层双引号，属于非合法JSON格式，BigQuery原生JSON解析函数无法直接识别，会返回null。
• 原有SQL逻辑错误：你存储的JSON结构全为嵌套对象，不存在数组结构，使用json_extract_array+UNNEST的操作完全不符合数据结构，就算JSON格式合法也无法提取到目标字段。

最优处理方案 #

先通过字符串替换修复JSON格式，再直接按嵌套层级提取字段即可，不需要用UNNEST操作，参考SQL如下：

SELECT
  ro.id,
  json_extract_scalar(clean_json, '$.inputs.Layer1.Layer2.Layer3.item1') AS item1,
  json_extract_scalar(clean_json, '$.inputs.Layer1.Layer2.Layer3.item2') AS item2,
  json_extract_scalar(clean_json, '$.inputs.Layer1.Layer2.Layer3.item3') AS item3
FROM (
  SELECT
    id,
    -- 先把所有双重双引号替换为单双引号，再去掉最外层的包裹双引号，得到合法JSON
    REGEXP_REPLACE(REPLACE(response, '""', '"'), r'^"|"$', '') AS clean_json
  FROM `project.dataset.table`
) ro

如果后续需要频繁处理该字段，可以基于原表创建一个视图，把clean_json的逻辑固化到视图中，后续查询直接用视图即可，不需要重复写转义逻辑。

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