如何在Oracle SQL中拆分JSON格式列并正确提取对应字段值

问题原因 #

你原来使用的正则逻辑存在错误：按[^:]+冒号分割的规则会把JSON键、相邻的键值和后续键名混在一起，根本无法正确匹配对应键的取值，而且如果值本身带冒号会完全失效。

最优方案（Oracle 12c及以上版本） #

Oracle 12c开始原生支持JSON操作，用JSON_VALUE函数直接按路径提取值，性能和准确率远高于正则实现。

SELECT
  JSON_VALUE(json_column, '$.senderName') AS senderName,
  JSON_VALUE(json_column, '$.senderCountry') AS senderCountry,
  JSON_VALUE(json_column, '$.senderAddress') AS senderAddress
FROM your_table;

补充说明：

• 把语句里的json_column替换为你存储JSON数据的实际列名，your_table替换为实际表名
• 需要确保列中存储的是合法JSON格式，你给出的示例JSON末尾缺失了"}，实际业务数据如果是合法格式即可正常执行
• 如果要做兼容处理，避免非法JSON报错，可以加DEFAULT NULL ON ERROR参数，示例：JSON_VALUE(json_column, '$.senderName' DEFAULT NULL ON ERROR) AS senderName

兼容方案（Oracle 11g及更低版本） #

如果数据库版本不支持原生JSON函数，可以用修正后的正则表达式提取：

SELECT
  REGEXP_REPLACE(REGEXP_SUBSTR(json_column, '"senderName":"(.*?)"', 1, 1), '.*:"|"$', '') AS senderName,
  REGEXP_REPLACE(REGEXP_SUBSTR(json_column, '"senderCountry":"(.*?)"', 1, 1), '.*:"|"$', '') AS senderCountry,
  REGEXP_REPLACE(REGEXP_SUBSTR(json_column, '"senderAddress":"(.*?)"', 1, 1), '.*:"|"$', '') AS senderAddress
FROM your_table;

逻辑说明：先用REGEXP_SUBSTR匹配对应键名+双引号包裹的整段值，再用REGEXP_REPLACE去掉前缀的键名、冒号和前后的双引号，仅保留取值内容，值里包含冒号、逗号也不会受影响。

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