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
