DBA Hub

📋Steps in this guide1/5

Mapping of JSON Data To and From SQL Object Types in Oracle Database 19c

Oracle database 19c allows JSON data to instantiate user-defined object type instances, and user defined object-type instances can be converted to JSON data.

oracle 19cconfigurationintermediate
by OracleDba
13 views
1

Setup

We are going to generate some data to test with using the and table.

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
-- DROP TABLE EMP PURGE;
-- DROP TABLE DEPT PURGE;

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 to User-Defined Object Type Instance (JSON_VALUE)

There is a big gotcha in this section. For the mappings to work properly, the element keys must be the same case as the object-type attributes. I've purposely kept everything in upper case. If we wanted to use lower case element names, I would have to double-quote the attribute names in the object types to make them lower case also. The tests will be run against the following test table. We can generate some JSON data representing a simplified version of an employee record. We can see we have a separate JSON document per employee. Create an object type called to represent this simplified employee structure. The function can use the user-defined object type in the returning clause, so we return the instantiated object types from a query, based on the source JSON data. Let's replace the test data with a single JSON document containing all the employee data. As expected, we have a single row in the table. The document has an "EMPLOYEES" key with an array of employees as a value. We create a nested table type called based on the row type we created previously. Using the type in the returning clause, we now see a single nested table object returned, populated with objects. Replace the test data with a JSON document for each department, where the JSON represents the department and the employees for that department. For each row we see a JSON object containing basic department information, as well as an array of employees for the department. We create an object type called to represent the department, including the employees nested table holding the employees for that department. Using the object type in the returning clause allows us to produce a department object per row, with the department made up of basic department data, as well as a nested table of employees populated with employee object types.

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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
-- DROP TABLE json_documents PURGE;

CREATE TABLE json_documents (
  id    NUMBER,
  data  VARCHAR2(4000),
  CONSTRAINT json_documents_is_json CHECK (data IS JSON)
);

TRUNCATE TABLE json_documents;

INSERT INTO json_documents (id, data)
SELECT rownum,
       JSON_OBJECT(
         'EMPNO' : empno,
         'ENAME' : ename,
         'JOB' : job
       )
FROM   emp;

COMMIT;

SET PAGESIZE 1000
COLUMN data FORMAT A50

SELECT * FROM json_documents;

        ID DATA
---------- --------------------------------------------------
         1 {"EMPNO":7369,"ENAME":"SMITH","JOB":"CLERK"}
         2 {"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN"}
         3 {"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN"}
         4 {"EMPNO":7566,"ENAME":"JONES","JOB":"MANAGER"}
         5 {"EMPNO":7654,"ENAME":"MARTIN","JOB":"SALESMAN"}
         6 {"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER"}
         7 {"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER"}
         8 {"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST"}
         9 {"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT"}
        10 {"EMPNO":7844,"ENAME":"TURNER","JOB":"SALESMAN"}
        11 {"EMPNO":7876,"ENAME":"ADAMS","JOB":"CLERK"}
        12 {"EMPNO":7900,"ENAME":"JAMES","JOB":"CLERK"}
        13 {"EMPNO":7902,"ENAME":"FORD","JOB":"ANALYST"}
        14 {"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK"}

14 rows selected.

SQL>

CREATE TYPE T_EMP_ROW AS OBJECT (
  EMPNO  NUMBER(4),
  ENAME  VARCHAR2(10),
  JOB    VARCHAR2(9)
);
/

SELECT JSON_VALUE(data, '$'
RETURNING t_emp_row
) AS employee
FROM   json_documents;

EMPLOYEE(EMPNO, ENAME, JOB)
--------------------------------------------------------------------------------
T_EMP_ROW(7369, 'SMITH', 'CLERK')
T_EMP_ROW(7499, 'ALLEN', 'SALESMAN')
T_EMP_ROW(7521, 'WARD', 'SALESMAN')
T_EMP_ROW(7566, 'JONES', 'MANAGER')
T_EMP_ROW(7654, 'MARTIN', 'SALESMAN')
T_EMP_ROW(7698, 'BLAKE', 'MANAGER')
T_EMP_ROW(7782, 'CLARK', 'MANAGER')
T_EMP_ROW(7788, 'SCOTT', 'ANALYST')
T_EMP_ROW(7839, 'KING', 'PRESIDENT')
T_EMP_ROW(7844, 'TURNER', 'SALESMAN')
T_EMP_ROW(7876, 'ADAMS', 'CLERK')
T_EMP_ROW(7900, 'JAMES', 'CLERK')
T_EMP_ROW(7902, 'FORD', 'ANALYST')
T_EMP_ROW(7934, 'MILLER', 'CLERK')

14 rows selected.

SQL>

TRUNCATE TABLE json_documents;

INSERT INTO json_documents (id, data)
VALUES (1, (
  SELECT JSON_OBJECT('EMPLOYEES' :
           JSON_ARRAYAGG (
             JSON_OBJECT(
               'EMPNO' : empno,
               'ENAME' : ename,
               'JOB' : job
             )
           )
         )
  FROM   emp));

COMMIT;

SET PAGESIZE 1000
COLUMN data FORMAT A50

SELECT * FROM json_documents;

        ID DATA
---------- --------------------------------------------------
         1 {"EMPLOYEES":[{"EMPNO":7369,"ENAME":"SMITH","JOB":
           "CLERK"},{"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALE
           SMAN"},{"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMA
           N"},{"EMPNO":7566,"ENAME":"JONES","JOB":"MANAGER"}
           ,{"EMPNO":7654,"ENAME":"MARTIN","JOB":"SALESMAN"},
           {"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER"},{"E
           MPNO":7782,"ENAME":"CLARK","JOB":"MANAGER"},{"EMPN
           O":7788,"ENAME":"SCOTT","JOB":"ANALYST"},{"EMPNO":
           7839,"ENAME":"KING","JOB":"PRESIDENT"},{"EMPNO":78
           44,"ENAME":"TURNER","JOB":"SALESMAN"},{"EMPNO":787
           6,"ENAME":"ADAMS","JOB":"CLERK"},{"EMPNO":7900,"EN
           AME":"JAMES","JOB":"CLERK"},{"EMPNO":7902,"ENAME":
           "FORD","JOB":"ANALYST"},{"EMPNO":7934,"ENAME":"MIL
           LER","JOB":"CLERK"}]}

1 row selected.

SQL>

CREATE TYPE T_EMP_TAB AS TABLE OF T_EMP_ROW;
/

SELECT JSON_VALUE(data, '$.EMPLOYEES'
RETURNING t_emp_tab
) AS employees
FROM   json_documents;

EMPLOYEES(EMPNO, ENAME, JOB)
--------------------------------------------------------------------------------
T_EMP_TAB(T_EMP_ROW(7369, 'SMITH', 'CLERK'), T_EMP_ROW(7499, 'ALLEN', 'SALESMAN'
), T_EMP_ROW(7521, 'WARD', 'SALESMAN'), T_EMP_ROW(7566, 'JONES', 'MANAGER'), T_E
MP_ROW(7654, 'MARTIN', 'SALESMAN'), T_EMP_ROW(7698, 'BLAKE', 'MANAGER'), T_EMP_R
OW(7782, 'CLARK', 'MANAGER'), T_EMP_ROW(7788, 'SCOTT', 'ANALYST'), T_EMP_ROW(783
9, 'KING', 'PRESIDENT'), T_EMP_ROW(7844, 'TURNER', 'SALESMAN'), T_EMP_ROW(7876,
'ADAMS', 'CLERK'), T_EMP_ROW(7900, 'JAMES', 'CLERK'), T_EMP_ROW(7902, 'FORD', 'A
NALYST'), T_EMP_ROW(7934, 'MILLER', 'CLERK'))

1 row selected.

SQL>

TRUNCATE TABLE json_documents;

INSERT INTO json_documents (id, data)
SELECT ROWNUM,
       JSON_OBJECT (
        'DEPTNO' : d.deptno,
        'DNAME' : d.dname,
        'EMPLOYEES' : (
          SELECT JSON_ARRAYAGG (
                   JSON_OBJECT(
                     'EMPNO' : e.empno,
                     'ENAME' : e.ename,
                     'JOB' : e.job
                   )
                 )
          FROM   emp e
          WHERE  e.deptno = d.deptno
        )
     ) AS department
FROM   dept d;

COMMIT;

SET PAGESIZE 1000
COLUMN data FORMAT A50

SELECT * FROM json_documents;

        ID DATA
---------- --------------------------------------------------
         1 {"DEPTNO":10,"DNAME":"ACCOUNTING","EMPLOYEES":[{"E
           MPNO":7782,"ENAME":"CLARK","JOB":"MANAGER"},{"EMPN
           O":7839,"ENAME":"KING","JOB":"PRESIDENT"},{"EMPNO"
           :7934,"ENAME":"MILLER","JOB":"CLERK"}]}

         2 {"DEPTNO":20,"DNAME":"RESEARCH","EMPLOYEES":[{"EMP
           NO":7369,"ENAME":"SMITH","JOB":"CLERK"},{"EMPNO":7
           566,"ENAME":"JONES","JOB":"MANAGER"},{"EMPNO":7788
           ,"ENAME":"SCOTT","JOB":"ANALYST"},{"EMPNO":7876,"E
           NAME":"ADAMS","JOB":"CLERK"},{"EMPNO":7902,"ENAME"
           :"FORD","JOB":"ANALYST"}]}

         3 {"DEPTNO":30,"DNAME":"SALES","EMPLOYEES":[{"EMPNO"
           :7499,"ENAME":"ALLEN","JOB":"SALESMAN"},{"EMPNO":7
           521,"ENAME":"WARD","JOB":"SALESMAN"},{"EMPNO":7654
           ,"ENAME":"MARTIN","JOB":"SALESMAN"},{"EMPNO":7698,
           "ENAME":"BLAKE","JOB":"MANAGER"},{"EMPNO":7844,"EN
           AME":"TURNER","JOB":"SALESMAN"},{"EMPNO":7900,"ENA
           ME":"JAMES","JOB":"CLERK"}]}

         4 {"DEPTNO":40,"DNAME":"OPERATIONS","EMPLOYEES":null
           }

4 rows selected.

SQL>

CREATE TYPE T_DEPT_ROW AS OBJECT (
  DEPTNO     NUMBER(2),
  DNAME      VARCHAR2(14),
  EMPLOYEES  T_EMP_TAB
); 
/

SELECT JSON_VALUE(data, '$'
RETURNING t_dept_row
) AS department
FROM   json_documents;

DEPARTMENT(DEPTNO, DNAME, EMPLOYEES(EMPNO, ENAME, JOB))
--------------------------------------------------------------------------------
T_DEPT_ROW(10, 'ACCOUNTING', T_EMP_TAB(T_EMP_ROW(7782, 'CLARK', 'MANAGER'), T_EM
P_ROW(7839, 'KING', 'PRESIDENT'), T_EMP_ROW(7934, 'MILLER', 'CLERK')))

T_DEPT_ROW(20, 'RESEARCH', T_EMP_TAB(T_EMP_ROW(7369, 'SMITH', 'CLERK'), T_EMP_RO
W(7566, 'JONES', 'MANAGER'), T_EMP_ROW(7788, 'SCOTT', 'ANALYST'), T_EMP_ROW(7876
, 'ADAMS', 'CLERK'), T_EMP_ROW(7902, 'FORD', 'ANALYST')))

T_DEPT_ROW(30, 'SALES', T_EMP_TAB(T_EMP_ROW(7499, 'ALLEN', 'SALESMAN'), T_EMP_RO
W(7521, 'WARD', 'SALESMAN'), T_EMP_ROW(7654, 'MARTIN', 'SALESMAN'), T_EMP_ROW(76
98, 'BLAKE', 'MANAGER'), T_EMP_ROW(7844, 'TURNER', 'SALESMAN'), T_EMP_ROW(7900,
'JAMES', 'CLERK')))

T_DEPT_ROW(40, 'OPERATIONS', NULL)

4 rows selected.

SQL>
3

On MISMATCH Clause

The tests in the previous section resulted in a perfect match between the JSON data and the object structures. This will not always be the case, so we need to understand what happens in the event of a mismatch between the data and the structure. The clause has the following definition. It can be applied to every possible mismatch in the statement, or limited to specific types of mismatch with the additional qualifiers. Populate the test table with simple employee data. Create two extra employee object types that will not match the JSON data in the test table. One with more and one with less attributes than the JSON data. Check the results of the previous query using these two types that don't match the data. If we want a mismatch to be ignored this is great, as both work, but what if this mismatch should be flagged as a problem? This is where the clause comes in handy. In the case of the object type, this will be seen as having missing data for the column. We might choose either of the following options to flag this mismatch as an error. In the first example, any possible mismatch will result in an error. In the second example, a mismatch of extra data will be ignored, but a mismatch of missing data will result in an error. Alternatively, we could just return NULL for the whole object type on a mismatch for missing data. In a similar way, we can deal with a mismatch based on extra data, as shown when we use the object type. So we have control on how to react to a data mismatch. Let's reset the test data ready for the next section.

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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
JSON_value_on_mismatch ( 
   ( IGNORE | ERROR | NULL ) 
    ON MISMATCH 
   [  ( (MISSING DATA) | (EXTRA DATA) | (TYPE ERROR) )  ]
  ) ...

TRUNCATE TABLE json_documents;

INSERT INTO json_documents (id, data)
SELECT rownum,
       JSON_OBJECT(
         'EMPNO' : empno,
         'ENAME' : ename,
         'JOB' : job
       )
FROM   emp;

COMMIT;


SELECT JSON_VALUE(data, '$' RETURNING t_emp_row) AS employee
FROM   json_documents;

EMPLOYEE(EMPNO, ENAME, JOB)
--------------------------------------------------------------------------------
T_EMP_ROW(7369, 'SMITH', 'CLERK')
T_EMP_ROW(7499, 'ALLEN', 'SALESMAN')
T_EMP_ROW(7521, 'WARD', 'SALESMAN')
T_EMP_ROW(7566, 'JONES', 'MANAGER')
T_EMP_ROW(7654, 'MARTIN', 'SALESMAN')
T_EMP_ROW(7698, 'BLAKE', 'MANAGER')
T_EMP_ROW(7782, 'CLARK', 'MANAGER')
T_EMP_ROW(7788, 'SCOTT', 'ANALYST')
T_EMP_ROW(7839, 'KING', 'PRESIDENT')
T_EMP_ROW(7844, 'TURNER', 'SALESMAN')
T_EMP_ROW(7876, 'ADAMS', 'CLERK')
T_EMP_ROW(7900, 'JAMES', 'CLERK')
T_EMP_ROW(7902, 'FORD', 'ANALYST')
T_EMP_ROW(7934, 'MILLER', 'CLERK')

14 rows selected.

SQL>

CREATE TYPE T_EMP_BIG_ROW AS OBJECT (
  EMPNO  NUMBER(4),
  ENAME  VARCHAR2(10),
  JOB    VARCHAR2(9),
  MGR    NUMBER(4)
);
/

CREATE TYPE T_EMP_SMALL_ROW AS OBJECT (
  EMPNO  NUMBER(4),
  ENAME  VARCHAR2(10)
);
/

SELECT JSON_VALUE(data, '$'
RETURNING t_emp_big_row
) AS employee
FROM   json_documents;

EMPLOYEE(EMPNO, ENAME, JOB, MGR)
--------------------------------------------------------------------------------
T_EMP_BIG_ROW(7369, 'SMITH', 'CLERK', NULL)
T_EMP_BIG_ROW(7499, 'ALLEN', 'SALESMAN', NULL)
T_EMP_BIG_ROW(7521, 'WARD', 'SALESMAN', NULL)
T_EMP_BIG_ROW(7566, 'JONES', 'MANAGER', NULL)
T_EMP_BIG_ROW(7654, 'MARTIN', 'SALESMAN', NULL)
T_EMP_BIG_ROW(7698, 'BLAKE', 'MANAGER', NULL)
T_EMP_BIG_ROW(7782, 'CLARK', 'MANAGER', NULL)
T_EMP_BIG_ROW(7788, 'SCOTT', 'ANALYST', NULL)
T_EMP_BIG_ROW(7839, 'KING', 'PRESIDENT', NULL)
T_EMP_BIG_ROW(7844, 'TURNER', 'SALESMAN', NULL)
T_EMP_BIG_ROW(7876, 'ADAMS', 'CLERK', NULL)
T_EMP_BIG_ROW(7900, 'JAMES', 'CLERK', NULL)
T_EMP_BIG_ROW(7902, 'FORD', 'ANALYST', NULL)
T_EMP_BIG_ROW(7934, 'MILLER', 'CLERK', NULL)

14 rows selected.

SQL>


SELECT JSON_VALUE(data, '$'
RETURNING t_emp_small_row
) AS employee
FROM   json_documents;

EMPLOYEE(EMPNO, ENAME)
--------------------------------------------------------------------------------
T_EMP_SMALL_ROW(7369, 'SMITH')
T_EMP_SMALL_ROW(7499, 'ALLEN')
T_EMP_SMALL_ROW(7521, 'WARD')
T_EMP_SMALL_ROW(7566, 'JONES')
T_EMP_SMALL_ROW(7654, 'MARTIN')
T_EMP_SMALL_ROW(7698, 'BLAKE')
T_EMP_SMALL_ROW(7782, 'CLARK')
T_EMP_SMALL_ROW(7788, 'SCOTT')
T_EMP_SMALL_ROW(7839, 'KING')
T_EMP_SMALL_ROW(7844, 'TURNER')
T_EMP_SMALL_ROW(7876, 'ADAMS')
T_EMP_SMALL_ROW(7900, 'JAMES')
T_EMP_SMALL_ROW(7902, 'FORD')
T_EMP_SMALL_ROW(7934, 'MILLER')

14 rows selected.

SQL>

SELECT JSON_VALUE(data, '$'
RETURNING t_emp_big_row ERROR ON MISMATCH
) AS employee
FROM   json_documents;
       *
ERROR at line 2:
ORA-40602: extra data for object type conversion


SQL>


SELECT JSON_VALUE(data, '$'
RETURNING t_emp_big_row ERROR ON MISMATCH (MISSING DATA)
) AS employee
FROM   json_documents;
       *
ERROR at line 2:
ORA-40602: extra data for object type conversion


SQL>

SELECT JSON_VALUE(data, '$'
RETURNING t_emp_big_row NULL ON MISMATCH (MISSING DATA)
) AS employee
FROM   json_documents;

EMPLOYEE(EMPNO, ENAME, JOB, MGR)
--------------------------------------------------------------------------------















14 rows selected.

SQL>

SELECT JSON_VALUE(data, '$'
RETURNING t_emp_small_row ERROR ON MISMATCH
) AS employee
FROM   json_documents;
       *
ERROR at line 2:
ORA-40602: extra data for object type conversion


SQL>


SELECT JSON_VALUE(data, '$'
RETURNING t_emp_small_row ERROR ON MISMATCH (EXTRA DATA)
) AS employee
FROM   json_documents;
       *
ERROR at line 2:
ORA-40602: extra data for object type conversion


SQL>

TRUNCATE TABLE json_documents;

INSERT INTO json_documents (id, data)
SELECT ROWNUM,
       JSON_OBJECT (
        'DEPTNO' : d.deptno,
        'DNAME' : d.dname,
        'EMPLOYEES' : (
          SELECT JSON_ARRAYAGG (
                   JSON_OBJECT(
                     'EMPNO' : e.empno,
                     'ENAME' : e.ename,
                     'JOB' : e.job
                   )
                 )
          FROM   emp e
          WHERE  e.deptno = d.deptno
        )
     ) AS department
FROM   dept d;

COMMIT;
4

User-Defined Object Type Instance to JSON (JSON_OBJECT and JSON_ARRAY)

Create a test table using the row type defined in the previous section. Notice we have to handle the nested table defined within the row type. We can populate it with instantiated object types using the query from the last example in the first section. If we query the data we get a representation of the object type instances stored in the rows. Using the function we can see the JSON representation of this data, stored using the user-defined object types. We can use the function to pretty-print this output to make it easier to read. The function will also convert user-defined object type instances to JSON. In the following example we create a JSON array for each row, containing the department number and the JSON representation of the department row. Once again, we can pretty-print it, if that helps you to understand the output.

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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
CREATE TABLE departments (
  department t_dept_row
)
NESTED TABLE department.employees STORE as departments_employees_nt;

INSERT INTO departments
SELECT JSON_VALUE(data, '$' RETURNING t_dept_row) AS department
FROM   json_documents;

COMMIT;

SELECT * FROM departments;

DEPARTMENT(DEPTNO, DNAME, EMPLOYEES(EMPNO, ENAME, JOB))
--------------------------------------------------------------------------------
T_DEPT_ROW(10, 'ACCOUNTING', T_EMP_TAB(T_EMP_ROW(7782, 'CLARK', 'MANAGER'), T_EM
P_ROW(7839, 'KING', 'PRESIDENT'), T_EMP_ROW(7934, 'MILLER', 'CLERK')))

T_DEPT_ROW(20, 'RESEARCH', T_EMP_TAB(T_EMP_ROW(7369, 'SMITH', 'CLERK'), T_EMP_RO
W(7566, 'JONES', 'MANAGER'), T_EMP_ROW(7788, 'SCOTT', 'ANALYST'), T_EMP_ROW(7876
, 'ADAMS', 'CLERK'), T_EMP_ROW(7902, 'FORD', 'ANALYST')))

T_DEPT_ROW(30, 'SALES', T_EMP_TAB(T_EMP_ROW(7499, 'ALLEN', 'SALESMAN'), T_EMP_RO
W(7521, 'WARD', 'SALESMAN'), T_EMP_ROW(7654, 'MARTIN', 'SALESMAN'), T_EMP_ROW(76
98, 'BLAKE', 'MANAGER'), T_EMP_ROW(7844, 'TURNER', 'SALESMAN'), T_EMP_ROW(7900,
'JAMES', 'CLERK')))

T_DEPT_ROW(40, 'OPERATIONS', NULL)

4 rows selected.

SQL>

SELECT
JSON_OBJECT(department)
FROM   departments;

JSON_OBJECT(DEPARTMENT)
--------------------------------------------------------------------------------
{"DEPTNO":10,"DNAME":"ACCOUNTING","EMPLOYEES":[{"EMPNO":7782,"ENAME":"CLARK","JO
B":"MANAGER"},{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT"},{"EMPNO":7934,"ENA
ME":"MILLER","JOB":"CLERK"}]}

{"DEPTNO":20,"DNAME":"RESEARCH","EMPLOYEES":[{"EMPNO":7369,"ENAME":"SMITH","JOB"
:"CLERK"},{"EMPNO":7566,"ENAME":"JONES","JOB":"MANAGER"},{"EMPNO":7788,"ENAME":"
SCOTT","JOB":"ANALYST"},{"EMPNO":7876,"ENAME":"ADAMS","JOB":"CLERK"},{"EMPNO":79
02,"ENAME":"FORD","JOB":"ANALYST"}]}

{"DEPTNO":30,"DNAME":"SALES","EMPLOYEES":[{"EMPNO":7499,"ENAME":"ALLEN","JOB":"S
ALESMAN"},{"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN"},{"EMPNO":7654,"ENAME":"
MARTIN","JOB":"SALESMAN"},{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER"},{"EMPNO
":7844,"ENAME":"TURNER","JOB":"SALESMAN"},{"EMPNO":7900,"ENAME":"JAMES","JOB":"C
LERK"}]}

{"DEPTNO":40,"DNAME":"OPERATIONS","EMPLOYEES":[]}

4 rows selected.

SQL>

SELECT JSON_SERIALIZE(
         JSON_OBJECT(department)
       PRETTY)
FROM   departments;

JSON_SERIALIZE(JSON_OBJECT(DEPARTMENT)PRETTY)
--------------------------------------------------------------------------------
{
  "DEPTNO" : 10,
  "DNAME" : "ACCOUNTING",
  "EMPLOYEES" :
  [
    {
      "EMPNO" : 7782,
      "ENAME" : "CLARK",
      "JOB" : "MANAGER"
    },
    {
      "EMPNO" : 7839,
      "ENAME" : "KING",
      "JOB" : "PRESIDENT"
    },
    {
      "EMPNO" : 7934,
      "ENAME" : "MILLER",
      "JOB" : "CLERK"
    }
  ]
}

{
  "DEPTNO" : 20,
  "DNAME" : "RESEARCH",
  "EMPLOYEES" :
  [
    {
      "EMPNO" : 7369,
      "ENAME" : "SMITH",
      "JOB" : "CLERK"
    },
    {
      "EMPNO" : 7566,
      "ENAME" : "JONES",
      "JOB" : "MANAGER"
    },
    {
      "EMPNO" : 7788,
      "ENAME" : "SCOTT",
      "JOB" : "ANALYST"
    },
    {
      "EMPNO" : 7876,
      "ENAME" : "ADAMS",
      "JOB" : "CLERK"
    },
    {
      "EMPNO" : 7902,
      "ENAME" : "FORD",
      "JOB" : "ANALYST"
    }
  ]
}

{
  "DEPTNO" : 30,
  "DNAME" : "SALES",
  "EMPLOYEES" :
  [
    {
      "EMPNO" : 7499,
      "ENAME" : "ALLEN",
      "JOB" : "SALESMAN"
    },
    {
      "EMPNO" : 7521,
      "ENAME" : "WARD",
      "JOB" : "SALESMAN"
    },
    {
      "EMPNO" : 7654,
      "ENAME" : "MARTIN",
      "JOB" : "SALESMAN"
    },
    {
      "EMPNO" : 7698,
      "ENAME" : "BLAKE",
      "JOB" : "MANAGER"
    },
    {
      "EMPNO" : 7844,
      "ENAME" : "TURNER",
      "JOB" : "SALESMAN"
    },
    {
      "EMPNO" : 7900,
      "ENAME" : "JAMES",
      "JOB" : "CLERK"
    }
  ]
}

{
  "DEPTNO" : 40,
  "DNAME" : "OPERATIONS",
  "EMPLOYEES" :
  [
  ]
}


4 rows selected.

SQL>

SELECT
JSON_ARRAY(d.department.deptno, department)
FROM   departments d;

JSON_ARRAY(D.DEPARTMENT.DEPTNO,DEPARTMENT)
--------------------------------------------------------------------------------
[10,{"DEPTNO":10,"DNAME":"ACCOUNTING","EMPLOYEES":[{"EMPNO":7782,"ENAME":"CLARK"
,"JOB":"MANAGER"},{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT"},{"EMPNO":7934,
"ENAME":"MILLER","JOB":"CLERK"}]}]

[20,{"DEPTNO":20,"DNAME":"RESEARCH","EMPLOYEES":[{"EMPNO":7369,"ENAME":"SMITH","
JOB":"CLERK"},{"EMPNO":7566,"ENAME":"JONES","JOB":"MANAGER"},{"EMPNO":7788,"ENAM
E":"SCOTT","JOB":"ANALYST"},{"EMPNO":7876,"ENAME":"ADAMS","JOB":"CLERK"},{"EMPNO
":7902,"ENAME":"FORD","JOB":"ANALYST"}]}]

[30,{"DEPTNO":30,"DNAME":"SALES","EMPLOYEES":[{"EMPNO":7499,"ENAME":"ALLEN","JOB
":"SALESMAN"},{"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN"},{"EMPNO":7654,"ENAM
E":"MARTIN","JOB":"SALESMAN"},{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER"},{"E
MPNO":7844,"ENAME":"TURNER","JOB":"SALESMAN"},{"EMPNO":7900,"ENAME":"JAMES","JOB
":"CLERK"}]}]

[40,{"DEPTNO":40,"DNAME":"OPERATIONS"}]

4 rows selected.

SQL>

SELECT JSON_SERIALIZE(
         JSON_ARRAY(d.department.deptno, department)
       PRETTY)
FROM   departments d;

JSON_SERIALIZE(JSON_ARRAY(D.DEPARTMENT.DEPTNO,DEPARTMENT)PRETTY)
--------------------------------------------------------------------------------
[
  10,
  {
    "DEPTNO" : 10,
    "DNAME" : "ACCOUNTING",
    "EMPLOYEES" :
    [
      {
        "EMPNO" : 7782,
        "ENAME" : "CLARK",
        "JOB" : "MANAGER"
      },
      {
        "EMPNO" : 7839,
        "ENAME" : "KING",
        "JOB" : "PRESIDENT"
      },
      {
        "EMPNO" : 7934,
        "ENAME" : "MILLER",
        "JOB" : "CLERK"
      }
    ]
  }
]

[
  20,
  {
    "DEPTNO" : 20,
    "DNAME" : "RESEARCH",
    "EMPLOYEES" :
    [
      {
        "EMPNO" : 7369,
        "ENAME" : "SMITH",
        "JOB" : "CLERK"
      },
      {
        "EMPNO" : 7566,
        "ENAME" : "JONES",
        "JOB" : "MANAGER"
      },
      {
        "EMPNO" : 7788,
        "ENAME" : "SCOTT",
        "JOB" : "ANALYST"
      },
      {
        "EMPNO" : 7876,
        "ENAME" : "ADAMS",
        "JOB" : "CLERK"
      },
      {
        "EMPNO" : 7902,
        "ENAME" : "FORD",
        "JOB" : "ANALYST"
      }
    ]
  }
]

[
  30,
  {
    "DEPTNO" : 30,
    "DNAME" : "SALES",
    "EMPLOYEES" :
    [
      {
        "EMPNO" : 7499,
        "ENAME" : "ALLEN",
        "JOB" : "SALESMAN"
      },
      {
        "EMPNO" : 7521,
        "ENAME" : "WARD",
        "JOB" : "SALESMAN"
      },
      {
        "EMPNO" : 7654,
        "ENAME" : "MARTIN",
        "JOB" : "SALESMAN"
      },
      {
        "EMPNO" : 7698,
        "ENAME" : "BLAKE",
        "JOB" : "MANAGER"
      },
      {
        "EMPNO" : 7844,
        "ENAME" : "TURNER",
        "JOB" : "SALESMAN"
      },
      {
        "EMPNO" : 7900,
        "ENAME" : "JAMES",
        "JOB" : "CLERK"
      }
    ]
  }
]

[
  40,
  {
    "DEPTNO" : 40,
    "DNAME" : "OPERATIONS"
  }
]


4 rows selected.

SQL>
5

Clean Up

We've created a number of objects with dependencies, so let's clean everything up. 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
DROP TABLE emp PURGE;
DROP TABLE dept PURGE;
DROP TABLE json_documents PURGE;
DROP TABLE departments PURGE;

DROP TYPE t_dept_row;
DROP TYPE t_emp_tab;
DROP TYPE t_emp_row;
DROP TYPE t_emp_big_row;
DROP TYPE t_emp_small_row;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!