8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- Setup
- JSON to User-Defined Object Type Instance (JSON_VALUE)
- On MISMATCH Clause
- User-Defined Object Type Instance to JSON (JSON_OBJECT and JSON_ARRAY)
- Clean Up
Related articles.
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Setup
We are going to generate some data to test with using the EMP and DEPT table.
-- 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;
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.
-- DROP TABLE json_documents PURGE; CREATE TABLE json_documents ( id NUMBER, data VARCHAR2(4000), CONSTRAINT json_documents_is_json CHECK (data IS JSON) );
We can generate some JSON data representing a simplified version of an employee record.
TRUNCATE TABLE json_documents;
INSERT INTO json_documents (id, data)
SELECT rownum,
JSON_OBJECT(
'EMPNO' : empno,
'ENAME' : ename,
'JOB' : job
)
FROM emp;
COMMIT;
We can see we have a separate JSON document per employee.
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 an object type called T_EMP_ROW to represent this simplified employee structure.
CREATE TYPE T_EMP_ROW AS OBJECT ( EMPNO NUMBER(4), ENAME VARCHAR2(10), JOB VARCHAR2(9) ); /
The JSON_VALUE 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.
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>
Let's replace the test data with a single JSON document containing all the employee data.
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;
As expected, we have a single row in the table. The document has an "EMPLOYEES" key with an array of employees as a value.
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>
We create a nested table type called T_EMP_TAB based on the T_EMP_ROW row type we created previously.
CREATE TYPE T_EMP_TAB AS TABLE OF T_EMP_ROW; /
Using the T_EMP_TAB type in the returning clause, we now see a single nested table object returned, populated with T_EMP_ROW objects.
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>
Replace the test data with a JSON document for each department, where the JSON represents the department and the employees for that department.
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;
For each row we see a JSON object containing basic department information, as well as an array of employees for the department.
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>
We create an object type called T_DEPT_ROW to represent the department, including the employees nested table T_EMP_TAB holding the employees for that department.
CREATE TYPE T_DEPT_ROW AS OBJECT ( DEPTNO NUMBER(2), DNAME VARCHAR2(14), EMPLOYEES T_EMP_TAB ); /
Using the T_DEPT_ROW 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.
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>
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 ON MISMATCH clause has the following definition.
JSON_value_on_mismatch (
( IGNORE | ERROR | NULL )
ON MISMATCH
[ ( (MISSING DATA) | (EXTRA DATA) | (TYPE ERROR) ) ]
) ...
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.
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 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.
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) ); /
Check the results of the previous query using these two types that don't match the data.
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>
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 ON MISMATCH clause comes in handy.
In the case of the T_EMP_BIG_ROW object type, this will be seen as having missing data for the MGR column. We might choose either of the following options to flag this mismatch as an error.
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>
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.
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>
In a similar way, we can deal with a mismatch based on extra data, as shown when we use the T_EMP_SMALL_ROW object type.
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>
So we have control on how to react to a data mismatch.
Let's reset the test data ready for the next section.
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;
User-Defined Object Type Instance to JSON (JSON_OBJECT and JSON_ARRAY)
Create a test table using the t_dept_row row type defined in the previous section. Notice we have to handle the nested table defined within the row type.
CREATE TABLE departments ( department t_dept_row ) NESTED TABLE department.employees STORE as departments_employees_nt;
We can populate it with instantiated object types using the query from the last example in the first section.
INSERT INTO departments SELECT JSON_VALUE(data, '$' RETURNING t_dept_row) AS department FROM json_documents; COMMIT;
If we query the data we get a representation of the object type instances stored in the rows.
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>
Using the JSON_OBJECT function we can see the JSON representation of this data, stored using the user-defined object types.
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>
We can use the JSON_SERIALIZE function to pretty-print this output to make it easier to read.
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>
The JSON_ARRAY 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.
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>
Once again, we can pretty-print it, if that helps you to understand the output.
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>
Clean Up
We've created a number of objects with dependencies, so let's clean everything up.
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;
For more information see:
- Example 16-3 Instantiate a User-Defined Object Instance From JSON Data with JSON_VALUE
- ON MISMATCH Clause for JSON_VALUE
- SQL/JSON Function JSON_OBJECT
- SQL/JSON Function JSON_ARRAY
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Hope this helps. Regards Tim...