BigQuery中JSON解析异常：json_extract与json_extract_scalar失效

• 先排查字符串的真实编码与结构
  直接用字符编码查看语句确认问题根源，比如执行：

  SELECT hex(shopaddress) FROM your_table LIMIT 1;
  

  通过十六进制编码可以精准判断首尾引号是否为ASCII标准双引号（编码0x22），是否存在全角引号、零宽空格、换行等肉眼不可见的特殊字符，这些都可能导致JSON解析失败。

• 精准清理字符串内容
  不要用默认的trim()（仅移除空白），改用指定移除首尾双引号的写法：

  SELECT json_extract(trim('"' FROM shopaddress), '$.storeId') FROM your_table;
  

  如果字符串前后还夹杂空白字符，可嵌套trim先清理空白：

  SELECT json_extract(trim('"' FROM trim(shopaddress)), '$.storeId') FROM your_table;
  

• 适配不同SQL引擎的JSON函数
  不同引擎的JSON解析函数存在差异，比如Spark SQL可尝试get_json_object：

  SELECT get_json_object(trim('"' FROM shopaddress), '$.storeId') FROM your_table;
  

  若需要解析完整结构，用from_json配合schema定义（以Spark SQL为例）：

  SELECT from_json(trim('"' FROM shopaddress), 
                   'storeId string, city string, axdcCode int, preferredDcs array<string>, omniChannel boolean, clusterId string, allocationProfileName string, hubType string, lat double, lng double, openTime int, closeTime int, storeType string, distance int, radius int, coldChain boolean, considerInventory boolean') 
  AS shop_info FROM your_table;
  

• 清理隐藏的特殊字符
  若字符串内部存在换行、制表符等干扰字符，先用replace移除：

  SELECT json_extract(trim('"' FROM replace(replace(shopaddress, '\n', ''), '\t', '')), '$.storeId') FROM your_table;
  

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