37.1 APEX_JSON 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;