如何在PL/SQL中使用JSON_KEY_LIST读取嵌套JSON_OBJECT_T的全部键

实现方案 #

核心思路是编写递归函数遍历所有JSON节点，遇到对象类型的属性值就继续深入遍历，收集全层级键：

步骤1：定义存储键的集合类型（如果已有可跳过） #

CREATE OR REPLACE TYPE varchar2_nt AS TABLE OF VARCHAR2(4000);
/

步骤2：创建递归获取全层级键的函数 #

CREATE OR REPLACE FUNCTION get_all_json_keys(p_json_obj JSON_OBJECT_T) RETURN varchar2_nt IS
    v_all_keys varchar2_nt := varchar2_nt();
    v_current_keys JSON_KEY_LIST;
    v_value JSON_ELEMENT_T;
    v_nested_keys varchar2_nt;
BEGIN
    -- 获取当前层所有键
    v_current_keys := p_json_obj.get_keys;
    
    FOR i IN 1..v_current_keys.COUNT LOOP
        -- 把当前键加入结果集
        v_all_keys.EXTEND;
        v_all_keys(v_all_keys.LAST) := v_current_keys(i);
        
        -- 检查当前键对应的值是否为嵌套对象
        v_value := p_json_obj.get(v_current_keys(i));
        IF v_value.is_object() THEN
            -- 递归获取嵌套对象的所有键
            v_nested_keys := get_all_json_keys(TREAT(v_value AS JSON_OBJECT_T));
            -- 把嵌套键合并到结果集
            FOR j IN 1..v_nested_keys.COUNT LOOP
                v_all_keys.EXTEND;
                v_all_keys(v_all_keys.LAST) := v_nested_keys(j);
            END LOOP;
        END IF;
        -- 如果需要处理数组类型的节点，可额外增加v_value.is_array()判断分支，遍历数组元素递归处理
    END LOOP;
    
    RETURN v_all_keys;
END;
/

步骤3：调用示例 #

DECLARE
    v_input_data JSON_OBJECT_T := JSON_OBJECT_T('{
       "REQS": {  "INDICATOR": "Y",
                  "NUMBER": 0,
                  "CATEGORY": "TU",
                  "ID_R": 10888,
                  "SUPPL_VAL": 0,
                  "line_itemssub": {
                                     "QTY": 0,
                                     "TOTAL_QTY": 1,
                                     "PIPE": {
                                               "P_CODE": 9801,
                                               "P_ID": 7500030,
                                               "CC_CODE": "C6AJG4"
                                              }
                                    }     
              },
       "Name":"Rajesh",
       "Age":47
    }');
    v_result varchar2_nt;
BEGIN
    v_result := get_all_json_keys(v_input_data);
    -- 遍历输出所有键
    FOR i IN 1..v_result.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_result(i));
    END LOOP;
END;
/

输出结果（全层级键） #

• REQS
• INDICATOR
• NUMBER
• CATEGORY
• ID_R
• SUPPL_VAL
• line_itemssub
• QTY
• TOTAL_QTY
• PIPE
• P_CODE
• P_ID
• CC_CODE
• Name
• Age

如果需要保留键的层级路径（比如输出REQS.line_itemssub.PIPE.P_CODE格式），可以修改递归逻辑，在传递的时候拼接上层键名即可。

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