
Oracle introduces SQL/JSON functions in Oracle Database 12c Release 2 (12.2). This guide will walk you through the basic examples of the SQL/JSON functions and how they works.
Setup
The examples mentioned 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>
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 JSON_ARRAY( (SELECT JSON_OBJECTAGG ( KEY 'department' VALUE 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 ) ) ) FROM dual; JSON_OBJECT(KEY'DEPARTMENTS'VALUEJSON_ARRAY((SELECTJSON_OBJECTAGG(KEY'DEPARTMENT'VALUEJSON_OBJECT(KEY'DEPARTMENT_NAME'VALUED.DNAME,KEY'DEPARTMENT_NO'VALUED.DEPT ---------------------------------------------------------------------------------------------------------------------------------------------------------------- {"departments":[{"department":{"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":{"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_numb er":7876,"employee_name":"ADAMS"},{"employee_number":7902,"employee_name":"FORD"}]},"department":{"department_name":"SALES","department_no":30,"employees":[{"em ployee_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":{"department_n ame":"OPERATIONS","department_no":40,"employees":null}}]} SQL>
If we run this through a JSON Formatter, we can see the structure better.
{ "departments":[ { "department":{ "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":{ "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":{ "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":{ "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 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
. All except the JSON_OBJECT
function can optionally return their output in CLOB
format.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;
The documentation states the default return type is
VARCHAR2(4000)
.FORMAT JSON Clause
The
FORMAT JSON
clause is optional and is provided for "semantic clarity". I don't understand what this means, but it does seem to have an effect on quoting.-- 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 numeric 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>
No comments: