8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
APEX_JSON Package: Generate and Parse JSON Documents in Oracle
Oracle Application Express (APEX) 5 includes the APEX_JSON
package, which allows you to generate and parse JSON documents in the database. Although the APEX_JSON
package is part of APEX, it can be used directly in PL/SQL without needing to enable or use APEX as a whole.
Related articles.
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Setup
This article assumes the following.
- You have an existing Oracle database with an APEX 5 installation.
- You have configured a mechanism for displaying the JSON documents (EPG, ORDS or mod_plsql), or you are planning to use a temporary CLOB to capture the output.
- You have access to the
EMP
andDEPT
tables from the SCOTT schema. If they are not present you can install them using the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script on the server, or build the tables in your local schema using the script below.
If you prefer to use local copies of the EMP
and DEPT
tables, they can be built using the following code.
CREATE TABLE DEPT ( DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, DNAME VARCHAR2(14), LOC VARCHAR2(13) ) ; CREATE TABLE EMP ( EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT ); INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON'); INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20); INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); COMMIT;
Generating JSON
Temporary CLOB
By default the JSON produced by the APEX_JSON
package is written out using the HTP
package, so you need a gateway (EPG, ORDS or mod_plsql) to see it. If instead you want to work with it inside PL/SQL, you can tell the package to write the JSON to a temporary CLOB. This is done using the following routines.
INITIALIZE_CLOB_OUTPUT
: Direct all writes to a temporary CLOB.GET_CLOB_OUTPUT
: Returns the current contents of the temporary CLOB.FREE_OUTPUT
: Frees all resources associated with the temporary CLOB.
The examples in this article use this method since it allows them to be self contained and runnable, without having to worry about the configuration of a gateway to show the output. This is purely for the convenience of this article, not a recommendation.
REF CURSOR to JSON
Probably the simplest way to generate JSON using the APEX_JSON
package is to convert a REF CURSOR
into a JSON document. The following example shows a simple query opened as a REF CURSOR
, which is subsequently sent to the WRITE
procedure. Notice the column names are aliased to change their names and double-quoted to force the case of the names.
SET SERVEROUTPUT ON DECLARE l_cursor SYS_REFCURSOR; BEGIN OPEN l_cursor FOR SELECT e.empno AS "employee_number", e.ename AS "employee_name", e.deptno AS "department_number" FROM emp e WHERE rownum <= 2; APEX_JSON.initialize_clob_output; APEX_JSON.open_object; APEX_JSON.write('employees', l_cursor); APEX_JSON.close_object; DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output); APEX_JSON.free_output; END; / { "employees": [ { "employee_number": 7369, "employee_name": "SMITH", "department_number": 20 }, { "employee_number": 7499, "employee_name": "ALLEN", "department_number": 30 } ] } PL/SQL procedure successfully completed. SQL>
Nesting of JSON is possible using calls to the CURSOR
function in the select list. Notice the alias of the call to the CURSOR
function, which is used to name the array of employees.
SET SERVEROUTPUT ON DECLARE l_cursor SYS_REFCURSOR; BEGIN OPEN l_cursor FOR SELECT d.dname AS "department_name", d.deptno AS "department_number", CURSOR(SELECT e.empno AS "employee_number", e.ename AS "employee_name" FROM emp e WHERE e.deptno = d.deptno ORDER BY e.empno) AS "employees" FROM dept d ORDER BY d.dname; APEX_JSON.initialize_clob_output; APEX_JSON.open_object; APEX_JSON.write('departments', l_cursor); APEX_JSON.close_object; DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output); APEX_JSON.free_output; END; / { "departments": [ { "department_name": "ACCOUNTING", "department_number": 10, "employees": [ { "employee_number": 7782, "employee_name": "CLARK" }, { "employee_number": 7839, "employee_name": "KING" }, { "employee_number": 7934, "employee_name": "MILLER" } ] }, { "department_name": "OPERATIONS", "department_number": 40, "employees": null }, { "department_name": "RESEARCH", "department_number": 20, "employees": [ { "employee_number": 7369, "employee_name": "SMITH" }, { "employee_number": 7566, "employee_name": "JONES" }, { "employee_number": 7788, "employee_name": "SCOTT" }, { "employee_number": 7876, "employee_name": "ADAMS" }, { "employee_number": 7902, "employee_name": "FORD" } ] }, { "department_name": "SALES", "department_number": 30, "employees": [ { "employee_number": 7499, "employee_name": "ALLEN" }, { "employee_number": 7521, "employee_name": "WARD" }, { "employee_number": 7654, "employee_name": "MARTIN" }, { "employee_number": 7698, "employee_name": "BLAKE" }, { "employee_number": 7844, "employee_name": "TURNER" }, { "employee_number": 7900, "employee_name": "JAMES" } ] } ] } PL/SQL procedure successfully completed. SQL>
Manual JSON Build
If the JSON document is too complex to be represented by a single query, you can build the document in stages. The following example builds up a document containing a department and its employees, along with some metadata. It's not a complicated example, but shows how each piece of the document can be built separately if needed.
SET SERVEROUTPUT ON DECLARE l_deptno dept.deptno%TYPE := 10; l_dept_row dept%ROWTYPE; BEGIN APEX_JSON.initialize_clob_output; APEX_JSON.open_object; -- { SELECT d.* INTO l_dept_row FROM dept d WHERE d.deptno = l_deptno; APEX_JSON.open_object('department'); -- department { APEX_JSON.write('department_number', l_dept_row.deptno); APEX_JSON.write('department_name', l_dept_row.dname); APEX_JSON.open_array('employees'); -- employees: [ FOR cur_rec IN (SELECT * FROM emp e WHERE e.deptno = l_deptno) LOOP APEX_JSON.open_object; -- { APEX_JSON.write('employee_number', cur_rec.empno); APEX_JSON.write('employee_name', cur_rec.ename); APEX_JSON.close_object; -- } employee END LOOP; APEX_JSON.close_array; -- ] employees APEX_JSON.close_object; -- } department APEX_JSON.open_object('metadata'); -- metadata { APEX_JSON.write('published_date', TO_CHAR(SYSDATE, 'DD-MON-YYYY')); APEX_JSON.write('publisher', 'oracle-base.com'); APEX_JSON.close_object; -- } metadata APEX_JSON.close_object; -- } DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output); APEX_JSON.free_output; END; / { "department": { "department_number": 10, "department_name": "ACCOUNTING", "employees": [ { "employee_number": 7782, "employee_name": "CLARK" }, { "employee_number": 7839, "employee_name": "KING" }, { "employee_number": 7934, "employee_name": "MILLER" } ] }, "metadata": { "published_date": "04-APR-2016", "publisher": "oracle-base.com" } } PL/SQL procedure successfully completed. SQL>
If you are manually concatenating strings together to form JSON, you may need to us the STRINGIFY
functions to return escaped JSON values.
XML to JSON
XML fragments can be converted to JSON fragments using an overload of the WRITE
procedure that accepts XMLTYPE
as a parameter.
DECLARE l_xml SYS.XMLTYPE := sys.xmltype('<departments> <department> <department_number>10</department_number> <department_name>ACCOUNTING</department_name> </department> <department> <department_number>20</department_number> <department_name>RESEARCH</department_name> </department> </departments>'); BEGIN APEX_JSON.initialize_clob_output; APEX_JSON.write(l_xml); DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output); APEX_JSON.free_output; END; / [ { "department_number": 10, "department_name": "ACCOUNTING" }, { "department_number": 20, "department_name": "RESEARCH" } ]
JSON to XML
JSON fragments can be converted to XML fragments using the TO_XMLTYPE
function.
SET SERVEROUTPUT ON DECLARE l_json VARCHAR2(32767); l_xml XMLTYPE; BEGIN l_json := '[ {"department_number":10,"department_name":"ACCOUNTING"}, {"department_number":20,"department_name":"RESEARCH"} ]'; l_xml := APEX_JSON.to_xmltype(l_json ); DBMS_OUTPUT.put_line(l_xml.getClobVal()); END; / <?xml version="1.0" encoding="UTF-8"?> <json> <row><department_number>10</department_number><department_name>ACCOUNTING</department_name></row> <row><department_number>20</department_number><department_name>RESEARCH</department_name></row> </json> PL/SQL procedure successfully completed. SQL>
Combining this with XMLTABLE allows you to indirectly project columns on to JSON and query it like a table.
SELECT deptno, dname FROM XMLTABLE( '/json/row' PASSING APEX_JSON.to_xmltype('[{"department_number":10,"department_name":"ACCOUNTING"}, {"department_number":20,"department_name":"RESEARCH"}]') COLUMNS deptno NUMBER PATH '/row/department_number', dname VARCHAR2(14) PATH '/row/department_name' ); DEPTNO DNAME ---------- -------------- 10 ACCOUNTING 20 RESEARCH 2 rows selected. SQL>
Parsing JSON
For simple documents, you may find it simpler to use the XMLTABLE method described above.
The functionality for parsing JSON documents is quite basic, but will feel familiar to anyone who has parsed XML documents using PL/SQL. The basic components are listed below.
PARSE
: Parse a JSON document held in aCLOB
,VARCHAR2
orTABLE OF VARCHAR2
. The resulting array of members and values is used by the other procedures and functions. All the procedures and functions accept ap_values
parameter to specify a variable of typeAPEX_JSON.T_VALUES
to hold the array. This is only necessary if you want to expose the array in your code. If this parameter is omitted, the defaultg_values
package variable is used instead, which is much neater.GET_<data-type>
: Get the value of a specific member identified by a path. There are different functions for a variety of return types.GET_COUNT
: Returns the number of elements that make up an array identified by a path.GET_MEMBERS
: Returns a list of the members below the specified path. Think of it as the immediate children of a tree node.DOES_EXIST
: Check if a specified member exists.FIND_PATHS_LIKE
: A basic search for matching members based on a path, subpath and value.
In APEX 5.0.2, the parse procedure does not seem very tolerant of whitespace at the start of the lines. It's fine with tabs or no indentations. This seems to be fixed in 5.0.3.
The best way to get to grips with these routines is to play around with them by attempting to parse a variety of documents. The code below gives an example of several of these functions and procedures in action.
SET SERVEROUTPUT ON DECLARE l_json_text VARCHAR2(32767); l_count PLS_INTEGER; l_members WWV_FLOW_T_VARCHAR2; l_paths APEX_T_VARCHAR2; l_exists BOOLEAN; BEGIN l_json_text := '{ "department": { "department_number": 10, "department_name": "ACCOUNTING", "employees": [ { "employee_number": 7782, "employee_name": "CLARK" }, { "employee_number": 7839, "employee_name": "KING" }, { "employee_number": 7934, "employee_name": "MILLER" } ] }, "metadata": { "published_date": "04-APR-2016", "publisher": "oracle-base.com" } }'; APEX_JSON.parse(l_json_text); DBMS_OUTPUT.put_line('----------------------------------------'); DBMS_OUTPUT.put_line('Department Information (Basic path lookup)'); DBMS_OUTPUT.put_line('Department Number : ' || APEX_JSON.get_number(p_path => 'department.department_number')); DBMS_OUTPUT.put_line('Department Name : ' || APEX_JSON.get_varchar2(p_path => 'department.department_name')); DBMS_OUTPUT.put_line('----------------------------------------'); DBMS_OUTPUT.put_line('Employee Information (Loop through array)'); l_count := APEX_JSON.get_count(p_path => 'department.employees'); DBMS_OUTPUT.put_line('Employees Count : ' || l_count); FOR i IN 1 .. l_count LOOP DBMS_OUTPUT.put_line('Employee Item Idx : ' || i); DBMS_OUTPUT.put_line('Employee Number : ' || APEX_JSON.get_number(p_path => 'department.employees[%d].employee_number', p0 => i)); DBMS_OUTPUT.put_line('Employee Name : ' || APEX_JSON.get_varchar2(p_path => 'department.employees[%d].employee_name', p0 => i)); END LOOP; DBMS_OUTPUT.put_line('----------------------------------------'); DBMS_OUTPUT.put_line('Check elements (members) below a path'); l_members := APEX_JSON.get_members(p_path=>'department'); DBMS_OUTPUT.put_line('Members Count : ' || l_members.COUNT); FOR i IN 1 .. l_members.COUNT LOOP DBMS_OUTPUT.put_line('Member Item Idx : ' || i); DBMS_OUTPUT.put_line('Member Name : ' || l_members(i)); END LOOP; DBMS_OUTPUT.put_line('----------------------------------------'); DBMS_OUTPUT.put_line('Search for matching elements in an array'); l_paths := APEX_JSON.find_paths_like (p_return_path => 'department.employees[%]', p_subpath => '.employee_name', p_value => 'MILLER' ); DBMS_OUTPUT.put_line('Matching Paths : ' || l_paths.COUNT); FOR i IN 1 .. l_paths.COUNT loop DBMS_OUTPUT.put_line('Employee Number : ' || APEX_JSON.get_number(p_path => l_paths(i)||'.employee_number')); DBMS_OUTPUT.put_line('Employee Name : ' || APEX_JSON.get_varchar2(p_path => l_paths(i)||'.employee_name')); END LOOP; DBMS_OUTPUT.put_line('----------------------------------------'); DBMS_OUTPUT.put_line('Check if path exists'); l_exists := APEX_JSON.does_exist (p_path => 'department.employees[%d].employee_name', p0 => 4); DBMS_OUTPUT.put('Employee 4 Exists : '); IF l_exists THEN DBMS_OUTPUT.put_line('True'); ELSE DBMS_OUTPUT.put_line('False'); END IF; DBMS_OUTPUT.put_line('----------------------------------------'); DBMS_OUTPUT.put_line('Metadata (Basic path lookup)'); DBMS_OUTPUT.put_line('Department Number : ' || APEX_JSON.get_date(p_path => 'metadata.published_date', p_format => 'DD-MON-YYYY')); DBMS_OUTPUT.put_line('Department Name : ' || APEX_JSON.get_varchar2(p_path => 'metadata.publisher')); DBMS_OUTPUT.put_line('----------------------------------------'); END; / ---------------------------------------- Department Information (Basic path lookup) Department Number : 10 Department Name : ACCOUNTING ---------------------------------------- Employee Information (Loop through array) Employees Count : 3 Employee Item Idx : 1 Employee Number : 7782 Employee Name : CLARK Employee Item Idx : 2 Employee Number : 7839 Employee Name : KING Employee Item Idx : 3 Employee Number : 7934 Employee Name : MILLER ---------------------------------------- Check elements (members) below a path Members Count : 3 Member Item Idx : 1 Member Name : department_number Member Item Idx : 2 Member Name : department_name Member Item Idx : 3 Member Name : employees ---------------------------------------- Search for matching elements in an array Matching Paths : 1 Employee Number : 7934 Employee Name : MILLER ---------------------------------------- Check if path exists Employee 4 Exists : False ---------------------------------------- Metadata (Basic path lookup) Department Number : 04-APR-2016 00:00:00 Department Name : oracle-base.com ---------------------------------------- PL/SQL procedure successfully completed. SQL>
For more information see:
- APEX_JSON
- XMLTABLE
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Hope this helps. Regards Tim...