Oracle 19c提取CLOB内动态JSON字段的SQL/PLSQL实现

场景说明 #

在Oracle 19c环境下，某表的CLOB字段存储如下结构的JSON数据：

{
    "res": {
        "Pass": ["dUZ1OvFgRWKAU","cGauHbTWQ369w","cWcclj4oQvO4N"],
        "Fail": {
            "The below token is not valid": ["587XXXX9","556XXXX6","556XXXX0"],
            "Requested token was not found.": ["edgDmwSxpcMhMf0C9","d8T8S62SJw"]
        }
    },
    "err_msg": null
}

其中Pass和err_msg字段固定，Fail下的子字段数量、名称均不固定（可增删或变更），需要将Pass、Fail及err_msg的内容提取为如下行列格式：

status                                      ids
-----------------------------------------------------------
pass                                        dUZ1OvFgRWKAU
pass                                        cGauHbTWQ369w
fail-The below token is not valid           587XXXX9
fail-The below token is not valid           556XXXX0
fail-Requested token was not found.         edgDmwSxpcMhMf0C9
err_msg                                     null

SQL实现方案 #

利用Oracle 19c的JSON函数（JSON_TABLE、JSON_KEY_LIST）可以实现动态提取，以下是完整SQL示例（假设表名为your_table，CLOB字段名为json_clob）：

-- 提取Pass部分的数据
SELECT 'pass' AS status, id AS ids
FROM your_table,
     JSON_TABLE(json_clob, '$.res.Pass[*]' COLUMNS id VARCHAR2(100) PATH '$')
UNION ALL
-- 动态提取Fail部分的数据
SELECT 'fail-' || fail_key AS status, id AS ids
FROM your_table,
     -- 获取Fail下的所有动态键
     JSON_TABLE(JSON_KEY_LIST(json_clob, '$.res.Fail'), '$[*]' COLUMNS fail_key VARCHAR2(200) PATH '$') keys,
     -- 根据每个键展开对应的数组
     JSON_TABLE(json_clob, '$.res.Fail."' || fail_key || '"[*]' COLUMNS id VARCHAR2(100) PATH '$')
UNION ALL
-- 提取err_msg部分
SELECT 'err_msg' AS status, json_value(json_clob, '$.err_msg') AS ids
FROM your_table
ORDER BY status, ids;

代码说明 #

1. Pass部分：通过JSON_TABLE直接展开$.res.Pass数组，每行对应一个Pass的ID，status固定为pass。
2. Fail部分：
   • 先用JSON_KEY_LIST获取$.res.Fail下的所有动态键，生成临时的键列表。
   • 再针对每个键，用JSON_TABLE展开对应的数组，status拼接为fail-加键名。
3. err_msg部分：用JSON_VALUE直接提取$.err_msg的值，status固定为err_msg。
4. 最后用UNION ALL合并三部分结果并排序。

PL/SQL备选方案 #

如果需要更灵活的处理（比如复杂的异常捕获），可以用PL/SQL实现：

DECLARE
    v_json_clob CLOB;
    v_pass_ids JSON_ARRAY_T;
    v_fail_obj JSON_OBJECT_T;
    v_err_msg VARCHAR2(100);
    v_keys JSON_KEY_LIST;
    v_fail_array JSON_ARRAY_T;
BEGIN
    -- 假设从表中获取JSON数据（可根据实际条件调整查询）
    SELECT json_clob INTO v_json_clob FROM your_table WHERE id = 1;
    
    -- 处理Pass部分
    v_pass_ids := JSON_ARRAY_T(JSON_VALUE(v_json_clob, '$.res.Pass' FORMAT JSON));
    FOR i IN 0..v_pass_ids.get_size-1 LOOP
        DBMS_OUTPUT.PUT_LINE('pass' || CHR(9) || v_pass_ids.get_string(i));
    END LOOP;
    
    -- 处理Fail部分
    v_fail_obj := JSON_OBJECT_T(JSON_VALUE(v_json_clob, '$.res.Fail' FORMAT JSON));
    v_keys := v_fail_obj.get_keys;
    FOR i IN 1..v_keys.count LOOP
        v_fail_array := v_fail_obj.get_JSON_ARRAY(v_keys(i));
        FOR j IN 0..v_fail_array.get_size-1 LOOP
            DBMS_OUTPUT.PUT_LINE('fail-' || v_keys(i) || CHR(9) || v_fail_array.get_string(j));
        END LOOP;
    END LOOP;
    
    -- 处理err_msg部分
    v_err_msg := JSON_VALUE(v_json_clob, '$.err_msg');
    DBMS_OUTPUT.PUT_LINE('err_msg' || CHR(9) || v_err_msg);
END;
/

代码说明 #

1. 先从表中读取CLOB格式的JSON数据。
2. 用JSON_ARRAY_T和JSON_OBJECT_T解析JSON结构，遍历Pass数组输出每行数据。
3. 通过get_keys获取Fail的动态键，再遍历每个键对应的数组输出数据。
4. 最后提取并输出err_msg的值。

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