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: