DBA Hub

📋Steps in this guide1/7

APEX_JSON Package: Generate and Parse JSON Documents in Oracle

This article gives a brief overview of using the APEX_JSON package to generate and parse JSON documents in Oracle.

oracle miscconfigurationintermediate
by OracleDba
23 views
1

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 and 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 and tables, they can be built using the following code.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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;
2

Temporary CLOB

By default the JSON produced by the package is written out using the 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. - : Direct all writes to a temporary CLOB. - : Returns the current contents of the temporary CLOB. - : 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.
3

REF CURSOR to JSON

Probably the simplest way to generate JSON using the package is to convert a into a JSON document. The following example shows a simple query opened as a , which is subsequently sent to the procedure. Notice the column names are aliased to change their names and double-quoted to force the case of the names. Nesting of JSON is possible using calls to the function in the select list. Notice the alias of the call to the function, which is used to name the array of employees.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
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>

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>
4

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. If you are manually concatenating strings together to form JSON, you may need to us the functions to return escaped JSON values.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
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>
5

XML to JSON

XML fragments can be converted to JSON fragments using an overload of the procedure that accepts as a parameter.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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"
  }
]
6

JSON to XML

JSON fragments can be converted to XML fragments using the function. Combining this with XMLTABLE allows you to indirectly project columns on to JSON and query it like a table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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>

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>
7

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 a JSON document held in a , or . The resulting array of members and values is used by the other procedures and functions. All the procedures and functions accept a parameter to specify a variable of type 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 package variable is used instead, which is much neater. - : Get the value of a specific member identified by a path. There are different functions for a variety of return types. - : Returns the number of elements that make up an array identified by a path. - : Returns a list of the members below the specified path. Think of it as the immediate children of a tree node. - : Check if a specified member exists. - : 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. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!