APEX_JSON Nested JSON data parsing
{ "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
No comments:
Post a Comment
Please do not add any spam links or abusive comments.