37.1 Package Overview and Examples
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 (for example,
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 to the SYS.HTP buffer. To write to a temporary CLOB instead, use initialize_clob_output()
, get_clob_output()
, and free_output()
for managing the output buffer.
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;
Parent topic: APEX_JSON