APEX_JSON Nested parsing

APEX_JSON Nested JSON data parsing

Sample JSON

{

 "RAW": {

  "LEVEL_1": "2",

  "LEVEL_1_ARRAY": [{

    "LEVEL_2": {

     "LEVEL_2_A": "Ashish",

     "LEVEL_2_ARRAY": [{

       "LEVEL_3_A": "00",

       "LEVEL_3_B": "ABC"

      },

      {

       "LEVEL_3_A": "01",

       "LEVEL_3_B": "CAB"

      }

     ],

     "LEVEL_2_B": {

      "LEVEL_3_C": "",

      "LEVEL_3_D": ""

     },

     "LEVEL_2_C": {

      "LEVEL_3_C_1": "",

      "LEVEL_3_C_2": "A145543"

     }

    }

   },

   {

    "LEVEL_2": {

     "LEVEL_2_A": "Ashish",

     "LEVEL_2_ARRAY": [{

       "LEVEL_3_A": "00",

       "LEVEL_3_B": "ABC"

      },

      {

       "LEVEL_3_A": "01",

       "LEVEL_3_B": "CAB"

      }

     ],

     "LEVEL_2_B": {

      "LEVEL_3_C": "",

      "LEVEL_3_D": ""

     },

     "LEVEL_2_C": {

      "LEVEL_3_C_1": "",

      "LEVEL_3_C_2": "A145543"

     }

    }

   }

  ]

 }

}


Sample PLSQL block





DECLARE

    l_json_text   VARCHAR2 (32767);

    l_count       PLS_INTEGER;

    l_members     wwv_flow_t_varchar2;

    l_paths       apex_t_varchar2;

    l_exists      BOOLEAN;

    l_count_2     PLS_INTEGER;

BEGIN

    l_json_text := '{

 "RAW": {

  "LEVEL_1": "20",

  "LEVEL_1_ARRAY": [{

    "LEVEL_2": {

     "LEVEL_2_A": "Ashish",

     "LEVEL_2_ARRAY": [{

       "LEVEL_3_A": "00",

       "LEVEL_3_B": "ABC"

      },

      {

       "LEVEL_3_A": "01",

       "LEVEL_3_B": "CAB"

      }

     ],

     "LEVEL_2_B": {

      "LEVEL_3_C": "LEVEL_3_C",

      "LEVEL_3_D": "LEVEL_3_D"

     },

     "LEVEL_2_C": {

      "LEVEL_3_C_1": "LEVEL_3_C_1",

      "LEVEL_3_C_2": "LEVEL_3_C_2"

     }

    }

   },

   {

    "LEVEL_2": {

     "LEVEL_2_A": "Sahay",

     "LEVEL_2_ARRAY": [{

       "LEVEL_3_A": "00",

       "LEVEL_3_B": "ABC"

      },

      {

       "LEVEL_3_A": "01",

       "LEVEL_3_B": "CAB"

      }

     ],

     "LEVEL_2_B": {

      "LEVEL_3_C": "LEVEL_3_C khashas",

      "LEVEL_3_D": "LEVEL_3_D aksjkajsjkasjka"

     },

     "LEVEL_2_C": {

      "LEVEL_3_C_1": "LEVEL_3_C_1 king",

      "LEVEL_3_C_2": "A145543"

     }

    }

   }

  ]

 }

}'   ;

   apex_json.parse (l_json_text);

    DBMS_OUTPUT.put_line ('----------------------------------------');

    DBMS_OUTPUT.put_line ( 'LEVEL_1 : ' || apex_json.get_varchar2 (p_path => 'RAW.LEVEL_1'));

   l_count := apex_json.get_count (p_path => 'RAW.LEVEL_1_ARRAY');

    DBMS_OUTPUT.put_line ('RAW.LEVEL_1_ARRAY count: ' || l_count);
    FOR i IN 1 .. l_count

    LOOP

        DBMS_OUTPUT.put_line ('RAW.LEVEL_1_ARRAY  Item Idx : ' || i);

        DBMS_OUTPUT.put_line (

               'LEVEL_2_A   : '

            || apex_json.get_varchar2 (

                   p_path   => 'RAW.LEVEL_1_ARRAY[%d].LEVEL_2.LEVEL_2_A',

                   p0       => i));

        l_count_2 :=

            apex_json.get_count (

                p_path   => 'RAW.LEVEL_1_ARRAY[%d].LEVEL_2.LEVEL_2_ARRAY',

                p0       => i);

        DBMS_OUTPUT.put_line ('LEVEL_2_ARRAY count   : ' || l_count_2);

        FOR j IN 1 .. l_count_2

        LOOP

            DBMS_OUTPUT.put_line (

                   ' LEVEL_3_A: '

                || apex_json.get_varchar2 (

                       p_path   => 'RAW.LEVEL_1_ARRAY[%d].LEVEL_2.LEVEL_2_ARRAY[%d].LEVEL_3_A',

                       p0       => i,

                       p1       => j));



            DBMS_OUTPUT.put_line (

                   'LEVEL_3_B : '

                || apex_json.get_varchar2 (

                       p_path   => 'RAW.LEVEL_1_ARRAY[%d].LEVEL_2.LEVEL_2_ARRAY[%d].LEVEL_3_B',

                       p0       => i,

                       p1       => j));

        END LOOP;



        DBMS_OUTPUT.put_line (

               'LEVEL_3_C   : '

            || apex_json.get_varchar2 (

                   p_path   => 'RAW.LEVEL_1_ARRAY[%d].LEVEL_2.LEVEL_2_B.LEVEL_3_C',

                   p0       => i));



        DBMS_OUTPUT.put_line (

               'LEVEL_3_D   : '

            || apex_json.get_varchar2 (

                   p_path   => 'RAW.LEVEL_1_ARRAY[%d].LEVEL_2.LEVEL_2_B.LEVEL_3_D',

                   p0       => i));



        DBMS_OUTPUT.put_line (

               'LEVEL_3_C_1   : '

            || apex_json.get_varchar2 (

                   p_path   => 'RAW.LEVEL_1_ARRAY[%d].LEVEL_2.LEVEL_2_C.LEVEL_3_C_1',

                   p0       => i));



        DBMS_OUTPUT.put_line (

               'LEVEL_3_C_2   : '

            || apex_json.get_varchar2 (

                   p_path   => 'RAW.LEVEL_1_ARRAY[%d].LEVEL_2.LEVEL_2_C.LEVEL_3_C_2',

                   p0       => i));

    END LOOP;

END;

/


OUTPUT:





LEVEL_1 : 20

RAW.LEVEL_1_ARRAY count: 2

RAW.LEVEL_1_ARRAY  Item Idx : 1

LEVEL_2_A   : Ashish

LEVEL_2_ARRAY count   : 2

LEVEL_3_A: 00

LEVEL_3_B : ABC
LEVEL_3_A: 01
LEVEL_3_B : CAB
LEVEL_3_C   : LEVEL_3_C
LEVEL_3_D   : LEVEL_3_D
LEVEL_3_C_1   : LEVEL_3_C_1
LEVEL_3_C_2   : LEVEL_3_C_2
RAW.LEVEL_1_ARRAY  Item Idx : 2
LEVEL_2_A   : Ashish
LEVEL_2_ARRAY count   : 2
LEVEL_3_A: 00
LEVEL_3_B : ABC
LEVEL_3_A: 01
LEVEL_3_B : CAB
LEVEL_3_C   : LEVEL_3_C khashas
LEVEL_3_D   : LEVEL_3_D aksjkajsjkasjka
LEVEL_3_C_1   : LEVEL_3_C_1 king
LEVEL_3_C_2   : A145543


You might Also like: