Oracle表JSON字段提取指定元素为列的技术求助

我有一张Oracle表TEST_TABLE，其中importdata列（CLOB类型）存储JSON数据，需要提取JSON中的HMETHOD（别名HMETH）和HPRCSN两个元素，将其展示为独立列。

测试用表及数据 #

create table TEST_TABLE(id number,importdata clob);  

insert into TEST_TABLE values (100,'{"ClassId":30074,"Attributes":[{"Name":"TYPE-SPEC","Value":"SJ;3;1"},{"Name":"HREF","Value":"-1"},{"Name":"HMETHOD","Value":"96"},{"Name":"GEO_METHOD","Value":"96"},{"Name":"HPRCSN","Value":2.7676}]}');
insert into TEST_TABLE values (101,'{"ClassId":30074,"Attributes":[{"Name":"TYPE-SPEC","Value":"SJ;3;1"},{"Name":"HREF","Value":"-1"},{"Name":"HMETHOD","Value":"96"},{"Name":"HPRCSN","Value":3.04}]}');
insert into TEST_TABLE values (102,'{"ClassId":30074,"Attributes":[{"Name":"TYPE-SPEC","Value":"SJ;3;1"},{"Name":"HREF","Value":"-1"},{"Name":"HMETHOD","Value":"96"},{"Name":"GEO_METHOD","Value":"96"},{"Name":"HPRCSN","Value":77.1814}]}');
insert into TEST_TABLE values (103,'{"ClassId":30074,"Attributes":[{"Name":"TYPE-SPEC","Value":"SJ;3;1"},{"Name":"HREF","Value":"-1"},{"Name":"HMETHOD","Value":"96"},{"Name":"GEO_METHOD","Value":"-1"},{"Name":"HPRCSN","Value":3.1121}]}');
insert into TEST_TABLE values (105,'{"ClassId":32000,"Attributes":[{"Name":"ID","Value":"69804"},{"Name":"HREF","Value":"-1"},{"Name":"HPRCSN","Value":"5"}]},{"Name":"HMETHOD","Value":"96"} ');
insert into TEST_TABLE values (106,'{"ClassId":32000,"Attributes":[{"Name":"ID","Value":"73576"},{"Name":"HREF","Value":"-1"},{"Name":"HPRCSN","Value":"5"}]},{"Name":"HMETHOD","Value":"96"}]} ');
insert into TEST_TABLE values (107,'{"ClassId":32000,"Attributes":[{"Name":"ID","Value":"73589"},{"Name":"HREF","Value":"-1"},{"Name":"HPRCSN","Value":"5"}]},{"Name":"HMETHOD","Value":"96"}]} ');
insert into TEST_TABLE values (108,'{"ClassId":32000,"Attributes":[{"Name":"ID","Value":"74015"},{"Name":"HREF","Value":"-1"},{"Name":"HPRCSN","Value":"5"}]},{"Name":"HMETHOD","Value":"96"}]} ');
commit;

遇到的问题 #

• 元素在JSON中的位置不固定，固定位置截取的方法完全失效
• HPRCSN的值格式不统一：整数带双引号，小数无引号，直接转换为数值时会出现格式错误

现有SQL（仅部分有效） #

select t1.id
,to_number(regexp_substr(replace(regexp_replace(importdata, '[^,[:digit:]]',''),',,',','),'[^,]+',15)) as HMETH
,to_number(regexp_substr(replace(regexp_replace(importdata, '[^,[:digit:]]',''),',,',','),'[^,]+',18)) as HPRCSN
from TEST_TABLE t1;

修正方案 #

方案一：使用Oracle原生JSON函数（推荐，Oracle 12c及以上版本） #

Oracle 12c及以上提供了原生JSON处理函数，能精准解析JSON结构，不受元素位置影响，同时自动处理数值格式问题。

注意：测试数据中部分JSON存在语法错误（如id=105、106的JSON，Attributes数组闭合后额外出现独立对象），需先修正JSON结构再解析：

SELECT 
    t.id,
    -- 提取HMETHOD的数值
    JSON_VALUE(
        -- 修正无效JSON：将Attributes数组外的HMETHOD对象合并到数组内
        REGEXP_REPLACE(t.importdata, '(\]\}),(\{"Name":"HMETHOD".*?\})', '\1,\2]'),
        '$.Attributes[*]?(@.Name == "HMETHOD").Value' 
        RETURNING NUMBER
    ) AS HMETH,
    -- 提取HPRCSN的数值
    JSON_VALUE(
        REGEXP_REPLACE(t.importdata, '(\]\}),(\{"Name":"HMETHOD".*?\})', '\1,\2]'),
        '$.Attributes[*]?(@.Name == "HPRCSN").Value' 
        RETURNING NUMBER
    ) AS HPRCSN
FROM TEST_TABLE t;

• JSON_VALUE配合JSON路径表达式$.Attributes[*]?(@.Name == "XXX")，能精准定位Attributes数组中Name匹配的元素，不受位置影响
• RETURNING NUMBER会自动识别带引号的整数和不带引号的小数，统一转换为数值类型

方案二：使用正则表达式（兼容Oracle 12c以下版本） #

如果无法使用原生JSON函数，可编写鲁棒性更强的正则表达式，不依赖元素位置，同时处理带引号/不带引号的数值：

SELECT 
    t.id,
    -- 提取HMETHOD的Value，匹配带/不带引号的数值
    TO_NUMBER(REGEXP_SUBSTR(t.importdata, '"HMETHOD":"?([0-9\.]+)"?', 1, 1, NULL, 1)) AS HMETH,
    -- 提取HPRCSN的Value，匹配带/不带引号的数值
    TO_NUMBER(REGEXP_SUBSTR(t.importdata, '"HPRCSN":"?([0-9\.]+)"?', 1, 1, NULL, 1)) AS HPRCSN
FROM TEST_TABLE t;

• 正则表达式"HMETHOD":"?([0-9\.]+)"?匹配"HMETHOD":后的内容，捕获数字和小数点组成的数值部分，不管是否带双引号
• 该方法避免了固定位置截取的缺陷，但如果JSON中存在嵌套结构或key值出现在字符串内容中，可能出现误匹配，仅作为原生JSON函数的替代方案

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