How to read keys from nested JSON_OBJECT_T using JSON_KEY_LIST in PL /SQL?

Question

I have a json_object_t type which looks like below.

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 is giving me only 3 values REQS,Name & Age. But I want the keys present in nested objects also. How can I achieve this ?

Thanks in advance.

You can refer this link - asktom.oracle.com/pls/apex/…

Ankit Bajpai
– Ankit Bajpai

2021-08-31 14:31:23 +00:00
Commented Aug 31, 2021 at 14:31 — Ankit Bajpai
– Ankit Bajpai, Commented Aug 31, 2021 at 14:31

Oli · Accepted Answer · 2021-08-31 17:03:19Z

2

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;

Output

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.

answered Aug 31, 2021 at 17:03

Oli

411 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sandeep Manchukonda Over a year ago

This works. Good idea to use recursion. Thanks !

Collectives™ on Stack Overflow

How to read keys from nested JSON_OBJECT_T using JSON_KEY_LIST in PL /SQL?

1 Answer 1

PL/SQL

Output

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

PL/SQL

Output

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related