Oracle SQL如何拆分提取varchar2字段中类JSON结构的属性值

你存储的内容不属于标准JSON格式：存在固定前缀json data、键和值未包裹双引号，无法直接调用Oracle原生JSON解析函数，可根据数据库版本选择以下两种方案：

方案1：转换为标准JSON后用原生函数解析（推荐，适用于Oracle 12cR2及以上版本） #

先通过字符串处理把非标准内容清洗为合法JSON结构，再调用官方JSON函数提取，容错性和性能更好。
示例SQL（假设表名为your_table，存储类JSON内容的字段为content_col）：

SELECT
  json_value(cleaned_json, '$.first') AS first_val,
  json_value(cleaned_json, '$.second') AS second_val
FROM (
  SELECT
    '{' || regexp_replace(
      -- 先去掉开头固定的json data前缀，只保留大括号及内部内容
      regexp_substr(content_col, '\{.*\}', 1, 1, 'n'),
      -- 给所有键值对的键、值补充双引号，转为标准JSON格式
      '([a-zA-Z0-9_]+)[ ]*:[ ]*([a-zA-Z0-9_]+)',
      '"\1":"\2"',
      1,
      0,
      'n'
    ) AS cleaned_json
  FROM your_table
);

说明：正则匹配参数'n'表示允许.匹配换行符，适配字段内容的换行格式。如果键/值包含下划线、数字以外的特殊字符，可按需调整正则的匹配规则。

方案2：直接用正则匹配提取（适用于Oracle 11g及更早无原生JSON函数的版本） #

不需要做全量格式转换，直接通过正则定位目标键对应的值即可，写法更简单。
示例SQL：

SELECT
  -- 提取first对应的值：匹配first:后到换行/空白/大括号前的内容
  trim(regexp_substr(content_col, 'first[ ]*:[ ]*([^[:space:]}]+)', 1, 1, 'i', 1)) AS first_val,
  -- 提取second对应的值
  trim(regexp_substr(content_col, 'second[ ]*:[ ]*([^[:space:]}]+)', 1, 1, 'i', 1)) AS second_val
FROM your_table;

说明：正则最后一个参数1表示取第一个捕获组的内容，trim用来清理值前后可能存在的多余空格，如果值本身包含空格，可调整正则的终止匹配规则。

注意事项 #

• 如果字段内的类JSON结构存在嵌套、值包含特殊符号/换行的场景，优先使用方案1，可根据实际格式补充清洗规则，比纯正则提取的稳定性高很多。
• 如果数据量较大，不建议在查询时实时做格式转换，可新增一列预清洗后的JSON类型字段，写入数据时就完成格式标准化，查询时直接取数性能更好。

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