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

Home » Articles » 12c » Here

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.

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

Hope this helps. Regards Tim...

Back to the Top.