8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

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.

Related articles.

Setup

This article assumes the following.

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.

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.

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:

Hope this helps. Regards Tim...

Back to the Top.