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.
Alternatively, use to_xmltype()
to convert a JSON string to an xmltype.
This package also contains procedures to generate JSON-formatted output. Use the overloaded open_%()
, close_%()
and write()
procedures for writing.
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;