Apex_json parsing Samples

APEX_JSON parsing Samples :

To read from a string that contains JSON data, first use parse() to convert the string to an internal format. Then use the get_% routines (e.g.get_varchar2(), get_number(), ...) to access the data and find_paths_like() to search.


From Oracle Docs

• Example 1

This example parses a JSON string and prints the value of member variable "a".

DECLARE
 s varchar2(32767) := '{ "a": 1, "b": ["hello", "world"]}';
BEGIN
 apex_json.parse(s);
 sys.dbms_output.put_line('a is '||apex_json.get_varchar2(p_path => 'a'));
END;

• Example 2

This example converts a JSON string to XML and uses XMLTABLE to query member values.

SELECT col1, col2 
FROM xmltable ( '/json/row' passing apex_json.to_xmltype               
('[
  {
    "col1": 1,
    "col2": "hello"
  },
  {
    "col1": 2,
    "col2": "world"
  }
]')  
columns  
col1 number path '/row/col1',  
col2 varchar2(5) path '/row/col2' 
);

Example 3 

This example writes a nested JSON object to the HTP buffer.

BEGIN    
apex_json.open_object; -- {     
apex_json.write('a', 1); -- "a":1     
apex_json.open_array('b'); -- ,"b":[     
apex_json.open_object; -- {     
apex_json.write('c',2); -- "c":2     
apex_json.close_object; -- }    
apex_json.write('hello'); -- ,"hello"    
apex_json.write('world'); -- ,"world"    
apex_json.close_all; -- ] -- }
END;

See more in details

https://www.ashishsahay.com/search/label/APEX_JSON