如何从含点号键的嵌套JSON提取值？json_extract返回空求解

一、你当前语句的问题 #

1. 大小写匹配错误：结构1里的目标字段是searchWrappingSourceID（首字母小写s），但你的提取路径写的是SearchWrappingSourceID（首字母大写S）——JSON的key是大小写敏感的，这直接导致匹配失败返回null。
2. 未覆盖第二种结构：你的语句只针对结构1做了提取，遇到结构2的JSON自然返回null。

二、正确的提取写法 #

根据你使用的SQL引擎，用条件判断结合JSON函数就能同时处理两种结构：

以MySQL为例 #

SELECT
  CASE
    -- 先判断是否是结构1
    WHEN JSON_CONTAINS_PATH(jp_source_keys, 'one', '$.com.google.search.SearchWrappingSource') THEN
      JSON_UNQUOTE(JSON_EXTRACT(jp_source_keys, '$.com.google.search.SearchWrappingSource.searchWrappingSourceID'))
    -- 再判断是否是结构2
    WHEN JSON_CONTAINS_PATH(jp_source_keys, 'one', '$.com.google.search.SearchIngestionSource') THEN
      JSON_UNQUOTE(JSON_EXTRACT(jp_source_keys, '$.com.google.search.SearchIngestionSource.searchSource'))
    -- 其他未知结构返回null
    ELSE NULL
  END AS search_source_id
FROM table1;

（注：JSON_UNQUOTE用来去掉提取结果的引号，直接得到字符串值）

以BigQuery为例 #

SELECT
  IFNULL(
    -- 先尝试提取结构1的字段，取不到就用结构2的
    JSON_EXTRACT_SCALAR(jp_source_keys, '$.com.google.search.SearchWrappingSource.searchWrappingSourceID'),
    JSON_EXTRACT_SCALAR(jp_source_keys, '$.com.google.search.SearchIngestionSource.searchSource')
  ) AS search_source_id
FROM table1;

三、处理这类复杂JSON的最佳实践 #

• 严格匹配key的大小写：JSON键名区分大小写，提取路径必须和原始JSON里的key完全一致，别想当然改大小写。
• 先判断结构再提取：用JSON自带的判断函数（比如JSON_CONTAINS_PATH、JSON_HAS_KEY）先识别当前行的JSON结构，再针对性提取，避免无效操作返回null。
• 用标量提取函数：优先用返回纯字符串/数值的函数（比如BigQuery的JSON_EXTRACT_SCALAR、MySQL的JSON_UNQUOTE组合），省去后续处理引号的麻烦。
• 处理边界情况：一定要加else分支处理未知结构的JSON，避免结果里出现意料之外的null或错误。
• 频繁查询就预解析：如果这个JSON列经常被查询，不如在ETL阶段就把它拆成结构化的普通列存储，既提升查询速度，也让SQL更易读。

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