json - 如何在 PL /SQL 中使用 JSON_KEY_LIST 从嵌套的 JSON_OBJECT_T 中读取键？

Question

我有一种json_object_t如下所示的类型。

v_input_data := new JSON_OBJECT({
   "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_input_data.get_keys只给我 3 个值REQS，Name& Age。但我也希望嵌套对象中存在键。我怎样才能做到这一点？

提前致谢。

标签： jsonoracleplsql

Answer 1

PL/SQL

SET SERVEROUTPUT ON;
DECLARE
    PROCEDURE json_keys (p_object IN JSON_OBJECT_T) IS
        v_object JSON_OBJECT_T;
        v_keys JSON_KEY_LIST;
        v_key VARCHAR2(4000);
    BEGIN
        v_keys := p_object.get_keys;
        FOR i IN v_keys.FIRST..v_keys.LAST LOOP
            v_key := v_keys(i);
            dbms_output.put_line(v_key);
            IF p_object.get(v_key).is_object THEN
                v_object := new JSON_OBJECT_T(p_object.get(v_key));
                json_keys(v_object);
            END IF;
        END LOOP;
    END json_keys;
BEGIN
    json_keys(new 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
    }'));
END;

输出

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


PL/SQL procedure successfully completed.