Home » Articles » Misc » Here

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.

• Setup
• Generating JSON
  
  • Temporary CLOB
  • REF CURSOR to JSON
  • Manual JSON Build
  • XML to JSON
  • JSON to XML
• Parsing JSON

Related articles.

• JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23ai, 26ai, 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 and DEPT 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.

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

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 a CLOB, VARCHAR2 or TABLE OF VARCHAR2. The resulting array of members and values is used by the other procedures and functions. All the procedures and functions accept a p_values parameter to specify a variable of type APEX_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 default g_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.

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, 23ai, 26ai, All Articles

Hope this helps. Regards Tim...

Back to the Top.

(追記) (追記ここまで)

Created: 2016年04月05日 Updated: 2019年02月20日

Contact Us

Home | Articles | Scripts | Blog | Certification | Videos | Misc | About

About Tim Hall
Copyright & Disclaimer