Thursday, 1 December 2016

SQL/JSON Functions in Oracle Database 12c Release 2

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>

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'm not sure I 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 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>