PL\SQL parse json code example

Example 1: PL\SQL parse json

SQL> CREATE OR REPLACE FUNCTION parser ( p_json CLOB )
 2                     RETURN CLOB AS
 3
 4     v_dg CLOB;
 5     v_array JSON_ARRAY_T;
 6     v_element JSON_ELEMENT_T;
 7     v_ret_val VARCHAR2(1000);
 8
 9     e_no_more_elements EXCEPTION;
10     PRAGMA EXCEPTION_INIT(e_no_more_elements,-30625); 
11
12   BEGIN
13
14     -- Yes, I selected from DUAL. Sorry. 
15     -- I'll explain later. For now please just
16     -- roll with it...
17     SELECT JSON_DATAGUIDE(p_json)
18       INTO v_dg
19       FROM DUAL; 
20 
21     -- The dataguide is a JSON array so it loads 
22     -- nicely into a local JSON_ARRAY_T variable 
23     v_array := JSON_ARRAY_T(v_dg); 
24 
25     -- Loop through the elements and add them to the 
26     -- return string. Stop when we run out of elements 
27     -- and ORA-30625 is thrown. 
28     DECLARE 
29       v_counter NUMBER := 0; 
30     BEGIN 
31       LOOP 
32         v_element := v_array.get(v_counter); 
33         v_ret_val := v_ret_val || v_element.to_string; 
34         v_counter := v_counter + 1; 
35       END LOOP; 
36     EXCEPTION 
37       WHEN e_no_more_elements THEN 
38         NULL; 
39       WHEN OTHERS THEN 
40        RAISE; 
41     END; 
42 
43     -- send back the list of elements that defines 
44     -- the list of elements - its all about schema 
45     -- on demand (SOD). Never mind - I just made that up. 
46     RETURN(v_ret_val); 
47 
48   END; 
49  / 
Function created.

Example 2: PL\SQL parse json

DECLARE 
   j APEX_JSON.t_values; 
   r_count number;
   field5members   WWV_FLOW_T_VARCHAR2;
   p0 number;
   BrandId VARCHAR2(10);
BEGIN
APEX_JSON.parse(j,'<INSERT_JSON_STRING>');
# Getting number of field3 elements
r_count := APEX_JSON.GET_COUNT(p_path=>'field3',p_values=>j);
dbms_output.put_line('Nr Records: ' || r_count);

# Looping for each element in field3
FOR i IN 1 .. r_count LOOP
# Getting field5 members for the ith member of field3
 field5members := APEX_JSON.GET_MEMBERS(p_path=>'field3[%d].field5',p_values=>j,p0=>i);
# Looping all field5 members
 FOR q in 1 .. field5members.COUNT LOOP
# Extracting BrandId
   BrandId := APEX_JSON.GET_VARCHAR2(p_path=>'field3[%d].field5.'||field5members(q) ,p_values=>j,p0=>i);
# Printing BrandId and Product Id
   dbms_output.put_line('Product Id ="'||field5members(q)||'" BrandId="'||BrandId ||'"');
 END LOOP;
END LOOP;
   
END;