DBA Hub

📋Steps in this guide1/10

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).

oracle 12cconfigurationintermediate
by OracleDba
14 views
1

Setup

The examples in this article use the following tables.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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;
2

JSON_OBJECT

The function converts a comma-separated list of key-value pairs into object members within a JSON object.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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>
3

JSON_OBJECTAGG

The aggregate function creates a single JSON object containing a list of object members formed by aggregating a key-value pair from each row.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
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>
4

JSON_ARRAY

The function converts a comma-separated list of expressions into a JSON array of JSON values. From Oracle database 23ai onward the function can accept a subquery as input, which brings it in line with the current SQL/JSON standard. - JSON_ARRAY Using Subqueries in Oracle Database 23ai

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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>
5

JSON_ARRAYAGG

The aggregate function, similar to the function, aggregates an expression from each row into a single JSON array.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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>
6

Complex JSON Objects

Each function call can itself be an expression, so they can easily be combined to create complex JSON objects. If we run this through a JSON Formatter , we can see the structure better.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
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>

{
  "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
    }
  ]
}
7

Handling NULLs

All of the SQL/JSON functions have the ability determine how null values are handled. The default is , but this can be altered to .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- 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>
8

RETURNING Clause

The SQL/JSON generation functions can optionally include a clause to specify how the value is returned. All are capable of returning a value of varying size specified using either or . The documentation states the default return type is . The and functions can optionally return their output in format. In addition, Oracle 18c added support for and types for all SQL/JSON generation functions.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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;
9

FORMAT JSON Clause

The 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 clause. Using it does seem to have an impact on how the JSON output is quoted.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 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>
10

Using Numerics as Keys

The SQL/JSON functions don't accept numerics as keys. If you need to force their use, simply use the TO_CHAR function to convert them to strings. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!