8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
SQL/JSON Generation Functions in Oracle Database 12c Release 2 (12.2)
This article gives basic examples of the SQL/JSON generation functions introduced in Oracle Database 12c Release 2 (12.2). Remember, there were SQL/JSON functions and conditions added in Oracle Database 12c Release 1 (12.1) also, as described here.
- Setup
- JSON_OBJECT
- JSON_OBJECTAGG
- JSON_ARRAY
- JSON_ARRAYAGG
- Complex JSON Objects
- Handling NULLs
- RETURNING Clause
- FORMAT JSON Clause
- Using Numerics as Keys
Related articles.
- SQL/JSON : Generate JSON from SQL
- JSON Support in Oracle Database 12c Release 1 (12.1.0.2)
- APEX_JSON Package: Generate and Parse JSON Documents in Oracle
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Setup
The examples in this article use the following tables.
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;
JSON_OBJECT
The JSON_OBJECT
function converts a comma-separated list of key-value pairs into object members within a JSON object.
SELECT JSON_OBJECT ( KEY 'department-number' VALUE d.deptno, KEY 'department-name' VALUE d.dname, KEY 'location' VALUE d.loc ) AS departments FROM dept d ORDER BY d.deptno; DEPARTMENTS -------------------------------------------------------------------------------- {"department-number":10,"department-name":"ACCOUNTING","location":"NEW YORK"} {"department-number":20,"department-name":"RESEARCH","location":"DALLAS"} {"department-number":30,"department-name":"SALES","location":"CHICAGO"} {"department-number":40,"department-name":"OPERATIONS","location":"BOSTON"} SQL>
JSON_OBJECTAGG
The JSON_OBJECTAGG
aggregate function creates a single JSON object containing a list of object members formed by aggregating a key-value pair from each row.
SELECT JSON_OBJECTAGG ( KEY d.dname VALUE d.deptno ) AS departments FROM dept d ORDER BY d.deptno; DEPARTMENTS -------------------------------------------------------------------------------- {"ACCOUNTING":10,"RESEARCH":20,"SALES":30,"OPERATIONS":40} SQL>
JSON_ARRAY
The JSON_ARRAY
function converts a comma-separated list of expressions into a JSON array of JSON values.
SELECT JSON_ARRAY( ROWNUM, JSON_OBJECT(KEY 'department_no' VALUE d.deptno), JSON_OBJECT(KEY 'department_name' VALUE d.dname) ) AS department_json_array FROM dept d; DEPARTMENT_JSON_ARRAY -------------------------------------------------------------------------------- [1,{"department_no":10},{"department_name":"ACCOUNTING"}] [2,{"department_no":20},{"department_name":"RESEARCH"}] [3,{"department_no":30},{"department_name":"SALES"}] [4,{"department_no":40},{"department_name":"OPERATIONS"}] SQL>
From Oracle database 23c onward the JSON_ARRAY
function can accept a subquery as input, which brings it in line with the current SQL/JSON standard.
JSON_ARRAYAGG
The JSON_ARRAYAGG
aggregate function, similar to the LISTAGG
function, aggregates an expression from each row into a single JSON array.
SELECT JSON_ARRAYAGG(e.ename) employee_array FROM emp e WHERE e.deptno = 20; EMPLOYEE_ARRAY -------------------------------------------------------------------------------- ["SMITH","JONES","SCOTT","ADAMS","FORD"] SQL> SELECT JSON_ARRAYAGG(e.ename ORDER BY e.ename) employee_array FROM emp e WHERE e.deptno = 20; EMPLOYEE_ARRAY -------------------------------------------------------------------------------- ["ADAMS","FORD","JONES","SCOTT","SMITH"] SQL>
Complex JSON Objects
Each function call can itself be an expression, so they can easily be combined to create complex JSON objects.
SELECT JSON_OBJECT ( KEY 'departments' VALUE ( SELECT JSON_ARRAYAGG( JSON_OBJECT ( KEY 'department_name' VALUE d.dname, KEY 'department_no' VALUE d.deptno, KEY 'employees' VALUE ( SELECT JSON_ARRAYAGG ( JSON_OBJECT( KEY 'employee_number' VALUE e.empno, KEY 'employee_name' VALUE e.ename ) ) FROM emp e WHERE e.deptno = d.deptno ) ) ) FROM dept d ) ) AS departments FROM dual; DEPARTMENTS -------------------------------------------------------------------------------- {"departments":[{"department_name":"ACCOUNTING","department_no":10,"employees":[ {"employee_number":7782,"employee_name":"CLARK"},{"employee_number":7839,"employ ee_name":"KING"},{"employee_number":7934,"employee_name":"MILLER"}]},{"departmen t_name":"RESEARCH","department_no":20,"employees":[{"employee_number":7369,"empl oyee_name":"SMITH"},{"employee_number":7566,"employee_name":"JONES"},{"employee_ number":7788,"employee_name":"SCOTT"},{"employee_number":7876,"employee_name":"A DAMS"},{"employee_number":7902,"employee_name":"FORD"}]},{"department_name":"SAL ES","department_no":30,"employees":[{"employee_number":7499,"employee_name":"ALL EN"},{"employee_number":7521,"employee_name":"WARD"},{"employee_number":7654,"em ployee_name":"MARTIN"},{"employee_number":7698,"employee_name":"BLAKE"},{"employ ee_number":7844,"employee_name":"TURNER"},{"employee_number":7900,"employee_name ":"JAMES"}]},{"department_name":"OPERATIONS","department_no":40,"employees":null }]} SQL>
If we run this through a JSON Formatter, we can see the structure better.
{ "departments" : [ { "department_name" : "ACCOUNTING", "department_no" : 10, "employees" : [ { "employee_number" : 7782, "employee_name" : "CLARK" }, { "employee_number" : 7839, "employee_name" : "KING" }, { "employee_number" : 7934, "employee_name" : "MILLER" } ] }, { "department_name" : "RESEARCH", "department_no" : 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_no" : 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" } ] }, { "department_name" : "OPERATIONS", "department_no" : 40, "employees" : null } ] }
Handling NULLs
All of the SQL/JSON functions have the ability determine how null values are handled. The default is NULL ON NULL
, but this can be altered to ABSENT ON NULL
.
-- Default NULL handling. SELECT JSON_OBJECT( KEY 'employee_name' VALUE e.ename, KEY 'commission' VALUE e.comm ) AS employees FROM emp e WHERE e.deptno = 10; EMPLOYEES -------------------------------------------------------------------------------- {"employee_name":"CLARK","commission":null} {"employee_name":"KING","commission":null} {"employee_name":"MILLER","commission":null} SQL> -- Explicit NULL ON NULL. SELECT JSON_OBJECT( KEY 'employee_name' VALUE e.ename, KEY 'commission' VALUE e.comm NULL ON NULL ) AS employees FROM emp e WHERE e.deptno = 10; EMPLOYEES -------------------------------------------------------------------------------- {"employee_name":"CLARK","commission":null} {"employee_name":"KING","commission":null} {"employee_name":"MILLER","commission":null} SQL> -- ABSENT ON NULL. SELECT JSON_OBJECT( KEY 'employee_name' VALUE e.ename, KEY 'commission' VALUE e.comm ABSENT ON NULL ) AS employees FROM emp e WHERE e.deptno = 10; EMPLOYEES -------------------------------------------------------------------------------- {"employee_name":"CLARK"} {"employee_name":"KING"} {"employee_name":"MILLER"} SQL>
RETURNING Clause
The SQL/JSON generation functions can optionally include a RETURNING
clause to specify how the value is returned. All are capable of returning a VARCHAR2
value of varying size specified using either BYTE
or CHAR
. The documentation states the default return type is VARCHAR2(4000)
. The JSON_OBJECTAGG
and JSON_ARRAYAGG
functions can optionally return their output in CLOB
format.
SELECT JSON_OBJECTAGG ( KEY d.dname VALUE d.deptno RETURNING VARCHAR2 ) AS departments FROM dept d ORDER BY d.deptno; SELECT JSON_OBJECTAGG ( KEY d.dname VALUE d.deptno RETURNING VARCHAR2(32767 BYTE) ) AS departments FROM dept d ORDER BY d.deptno; SELECT JSON_OBJECTAGG ( KEY d.dname VALUE d.deptno RETURNING VARCHAR2(50 CHAR) ) AS departments FROM dept d ORDER BY d.deptno; SELECT JSON_OBJECTAGG ( KEY d.dname VALUE d.deptno RETURNING CLOB ) AS departments FROM dept d ORDER BY d.deptno;
In addition, Oracle 18c added support for CLOB
and BLOB
types for all SQL/JSON generation functions.
FORMAT JSON Clause
The FORMAT JSON
clause is optional and is provided for "semantic clarity". For the most part Oracle understands if data is in JSON format, so this clause is redundant, but if you are supplying JSON in the form of a BLOB you must use the FORMAT JSON
clause. Using it does seem to have an impact on how the JSON output is quoted.
-- Default. The job is quoted. SELECT JSON_OBJECTAGG ( KEY e.ename VALUE e.job ) AS employees FROM emp e WHERE e.deptno = 10; EMPLOYEES -------------------------------------------------------------------------------- {"CLARK":"MANAGER","KING":"PRESIDENT","MILLER":"CLERK"} SQL> -- Explicit FORMAT JSON. Notice lack of quotes on job. SELECT JSON_OBJECTAGG ( KEY e.ename VALUE e.job FORMAT JSON ) AS employees FROM emp e WHERE e.deptno = 10; EMPLOYEES -------------------------------------------------------------------------------- {"CLARK":MANAGER,"KING":PRESIDENT,"MILLER":CLERK} SQL>
Using Numerics as Keys
The SQL/JSON functions don't accept numerics as keys.
SELECT JSON_OBJECTAGG ( KEY e.empno VALUE e.ename ) AS employees FROM emp e WHERE e.deptno = 10; KEY e.empno VALUE e.ename * ERROR at line 2: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER SQL>
If you need to force their use, simply use the TO_CHAR function to convert them to strings.
SELECT JSON_OBJECTAGG ( KEY TO_CHAR(e.empno) VALUE e.ename ) AS employees FROM emp e WHERE e.deptno = 10; EMPLOYEES -------------------------------------------------------------------------------- {"7782":"CLARK","7839":"KING","7934":"MILLER"} SQL>
For more information see:
- Generation of JSON Data With SQL/JSON Functions
- JSON_ARRAY
- JSON_ARRAYAGG
- JSON_OBJECT
- JSON_OBJECTAGG
- Database JSON Developer's Guide
- JSON - Introduction
- SQL/JSON : Generate JSON from SQL
- JSON Support in Oracle Database 12c Release 1 (12.1.0.2)
- APEX_JSON Package: Generate and Parse JSON Documents in Oracle
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Hope this helps. Regards Tim...