In this article, I will explain how easily you can generate, parse and use JSON data in the Oracle database. I used Oracle 19c for all examples in this writing. JSON enhancements have been made since Oracle Database release 12.2. Therefore, numerous of new functionalities have come after the first version of 12c has been released. The following data set has been used in the examples. You can also reach the metadata of the EMP table by the following link.
https://livesql.oracle.com/apex/livesql/file/content_O5AEB2HE08PYEPTGCFLZU9YCV.html
create table emp(
empno number(4,0),
ename varchar2(10),
job varchar2(9),
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0)
);
insert into emp values(7839, 'KING', 'PRESIDENT', null, to_date('17/11/1981','dd/mm/yyyy'), 5000, null, 10);
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(7566, 'JONES', 'MANAGER', 7839, to_date('2/4/1981','dd/mm/yyyy'), 2975, null, 20);
insert into emp values(7788, 'SCOTT', 'ANALYST', 7566, to_date('12/5/87','dd/mm/rr'), 3000, null, 20);
insert into emp values(7902, 'FORD', 'ANALYST', 7566, to_date('3/12/1981','dd/mm/yyyy'), 3000, null, 20);
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(7654, 'MARTIN', 'SALESMAN', 7698, to_date('28/9/1981','dd/mm/yyyy'), 1250, 1400, 30);
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('10/06/87', 'dd/mm/rr'), 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(7934, 'MILLER', 'CLERK', 7782, to_date('23/1/1982','dd/mm/yyyy'), 1300, null, 10);
commit;
Our data look like as follows.
SQL> SET LINESIZE 200
SQL> COLUMN EMPNO FORMAT A10
SQL> COLUMN ENAME FORMAT A20
SQL> COLUMN JOB FORMAT A20
SQL> COLUMN MGR FORMAT A5
SQL> COLUMN HIREDATE FORMAT A15
SQL> COLUMN SAL FORMAT A10
SQL> COLUMN COMM FORMAT A10
SQL> COLUMN DEPTNO FORMAT A10
SQL> COLUMN EMPNO FORMAT 99999999
SQL> COLUMN MGR FORMAT 99999999
SQL> COLUMN SAL FORMAT 99999999
SQL> COLUMN COMM FORMAT 99999999
SQL> COLUMN DEPTNO FORMAT 99999999
SQL>
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- -------------------- -------------------- --------- --------------- --------- --------- ---------
7839 KING PRESIDENT 17/11/1981 5000 10
7698 BLAKE MANAGER 7839 01/05/1981 2850 30
7782 CLARK MANAGER 7839 09/06/1981 2450 10
7566 JONES MANAGER 7839 02/04/1981 2975 20
7788 SCOTT ANALYST 7566 12/05/1987 3000 20
7902 FORD ANALYST 7566 03/12/1981 3000 20
7369 SMITH CLERK 7902 17/12/1980 800 20
7499 ALLEN SALESMAN 7698 20/02/1981 1600 300 30
7521 WARD SALESMAN 7698 22/02/1981 1250 500 30
7654 MARTIN SALESMAN 7698 28/09/1981 1250 1400 30
7844 TURNER SALESMAN 7698 08/09/1981 1500 0 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- -------------------- -------------------- --------- --------------- --------- --------- ---------
7876 ADAMS CLERK 7788 10/06/1987 1100 20
7900 JAMES CLERK 7698 03/12/1981 950 30
7934 MILLER CLERK 7782 23/01/1982 1300 10
14 sat²rlar² seildi.
Generating JSON Data
There are many functions that you can generate json data. I mentioned the most usable ones. You can combine them in order to create complex json objects. Basically, the intended usage of each one can be understandable from the examples below.
JSON_OBJECT
SELECT
JSON_OBJECT(
KEY 'name' VALUE e.ename,
KEY 'job' VALUE e.job,
KEY 'hiredate' VALUE e.hiredate
) AS json_data
FROM emp e
WHERE e.deptno = 30;
JSON_OBJECTAGG
Note that numeric data cannot be used as key in JSON functions.
SELECT
JSON_OBJECTAGG(
KEY to_char(e.empno) VALUE e.ename
) AS json_data
FROM emp e
WHERE e.deptno = 30
ORDER BY e.empno;
JSON_ARRAY
SELECT
JSON_ARRAY(
e.empno,
JSON_OBJECT(
KEY 'name' VALUE e.ename,
KEY 'job' VALUE e.job,
KEY 'hiredate' VALUE e.hiredate
)
) AS json_data
FROM emp e
ORDER BY e.empno;
JSON_ARRAYAGG
Unlike JSON_ARRAY, JSON_ARRAYAGG combines the result set into a single JSON array. The query result in SQL*Plus is formatted to look properly.
SQL> SELECT
2 JSON_ARRAYAGG(
3 JSON_OBJECT(
4 KEY 'name' VALUE e.ename,
5 KEY 'job' VALUE e.job,
6 KEY 'hiredate' VALUE e.hiredate
7 )
8 ORDER BY e.empno
9 ) AS json_data
10 FROM emp e
11 order by e.empno;
JSON_DATA
--------------------------------------------------------------------------------
[{"name":"SMITH","job":"CLERK","hiredate":"1980-12-17T00:00:00"},
{"name":"ALLEN","job":"SALESMAN","hiredate":"1981-02-20T00:00:00"},
{"name":"WARD","job":"SALESMAN","hiredate":"1981-02-22T00:00:00"},
{"name":"JONES","job":"MANAGER","hiredate":"1981-04-02T00:00:00"},
{"name":"MARTIN","job":"SALESMAN","hiredate":"1981-09-28T00:00:00"},
{"name":"BLAKE","job":"MANAGER","hiredate":"1981-05-01T00:00:00"},
{"name":"CLARK","job":"MANAGER","hiredate":"1981-06-09T00:00:00"},
{"name":"SCOTT","job":"ANALYST","hiredate":"1987-05-12T00:00:00"},
{"name":"KING","job":"PRESIDENT","hiredate":"1981-11-17T00:00:00"},
{"name":"TURNER","job":"SALESMAN","hiredate":"1981-09-08T00:00:00"},
{"name":"ADAMS","job":"CLERK","hiredate":"1987-06-10T00:00:00"},
{"name":"JAMES","job":"CLERK","hiredate":"1981-12-03T00:00:00"},
{"name":"FORD","job":"ANALYST","hiredate":"1981-12-03T00:00:00"},
{"name":"MILLER","job":"CLERK","hiredate":"1982-01-23T00:00:00"}]
SQL> SELECT
2 JSON_ARRAYAGG(e.ename) AS json_data
3 FROM emp e
4 order by e.empno;
JSON_DATA
--------------------------------------------------------------------------------
["KING","BLAKE","CLARK","JONES","SCOTT","FORD","SMITH","ALLEN","WARD","MARTIN","
TURNER","ADAMS","JAMES","MILLER"]
Converting row to JSON
You can use the wildcard “*” as input to the JSON_OBJECT function in order to convert table rows to JSON without key:value mapping.
SELECT JSON_OBJECT(*) FROM emp;
SELECT
JSON_ARRAYAGG (
JSON_OBJECT(*) RETURNING CLOB
) AS JSON_DOC
FROM emp;
[
{
"EMPNO":7839,
"ENAME":"KING",
"JOB":"PRESIDENT",
"MGR":null,
"HIREDATE":"1981-11-17T00:00:00",
"SAL":5000,
"COMM":null,
"DEPTNO":10
},
{
"EMPNO":7698,
"ENAME":"BLAKE",
"JOB":"MANAGER",
"MGR":7839,
"HIREDATE":"1981-05-01T00:00:00",
"SAL":2850,
"COMM":null,
"DEPTNO":30
},
{
"EMPNO":7782,
"ENAME":"CLARK",
"JOB":"MANAGER",
"MGR":7839,
"HIREDATE":"1981-06-09T00:00:00",
"SAL":2450,
"COMM":null,
"DEPTNO":10
},
{
"EMPNO":7566,
"ENAME":"JONES",
"JOB":"MANAGER",
"MGR":7839,
"HIREDATE":"1981-04-02T00:00:00",
"SAL":2975,
"COMM":null,
"DEPTNO":20
},
{
"EMPNO":7788,
"ENAME":"SCOTT",
"JOB":"ANALYST",
"MGR":7566,
"HIREDATE":"1987-05-12T00:00:00",
"SAL":3000,
"COMM":null,
"DEPTNO":20
},
{
"EMPNO":7902,
"ENAME":"FORD",
"JOB":"ANALYST",
"MGR":7566,
"HIREDATE":"1981-12-03T00:00:00",
"SAL":3000,
"COMM":null,
"DEPTNO":20
},
{
"EMPNO":7369,
"ENAME":"SMITH",
"JOB":"CLERK",
"MGR":7902,
"HIREDATE":"1980-12-17T00:00:00",
"SAL":800,
"COMM":null,
"DEPTNO":20
},
{
"EMPNO":7499,
"ENAME":"ALLEN",
"JOB":"SALESMAN",
"MGR":7698,
"HIREDATE":"1981-02-20T00:00:00",
"SAL":1600,
"COMM":300,
"DEPTNO":30
},
{
"EMPNO":7521,
"ENAME":"WARD",
"JOB":"SALESMAN",
"MGR":7698,
"HIREDATE":"1981-02-22T00:00:00",
"SAL":1250,
"COMM":500,
"DEPTNO":30
},
{
"EMPNO":7654,
"ENAME":"MARTIN",
"JOB":"SALESMAN",
"MGR":7698,
"HIREDATE":"1981-09-28T00:00:00",
"SAL":1250,
"COMM":1400,
"DEPTNO":30
},
{
"EMPNO":7844,
"ENAME":"TURNER",
"JOB":"SALESMAN",
"MGR":7698,
"HIREDATE":"1981-09-08T00:00:00",
"SAL":1500,
"COMM":0,
"DEPTNO":30
},
{
"EMPNO":7876,
"ENAME":"ADAMS",
"JOB":"CLERK",
"MGR":7788,
"HIREDATE":"1987-06-10T00:00:00",
"SAL":1100,
"COMM":null,
"DEPTNO":20
},
{
"EMPNO":7900,
"ENAME":"JAMES",
"JOB":"CLERK",
"MGR":7698,
"HIREDATE":"1981-12-03T00:00:00",
"SAL":950,
"COMM":null,
"DEPTNO":30
},
{
"EMPNO":7934,
"ENAME":"MILLER",
"JOB":"CLERK",
"MGR":7782,
"HIREDATE":"1982-01-23T00:00:00",
"SAL":1300,
"COMM":null,
"DEPTNO":10
}
]
JSON_DATAGUIDE
As the name suggests, JSON_DATAGUIDE function returns the metadata of the given JSON input.
SELECT
JSON_SERIALIZE(
JSON_DATAGUIDE(JSON_DATA)
PRETTY)
FROM (
SELECT
JSON_ARRAYAGG(JSON_OBJECT(*)) JSON_DATA
FROM EMP
);
[
{
"o:path" : "$.JOB",
"type" : "string",
"o:length" : 16
},
{
"o:path" : "$.MGR",
"type" : "string",
"o:length" : 4
},
{
"o:path" : "$.SAL",
"type" : "number",
"o:length" : 4
},
{
"o:path" : "$.COMM",
"type" : "string",
"o:length" : 4
},
{
"o:path" : "$.EMPNO",
"type" : "number",
"o:length" : 4
},
{
"o:path" : "$.ENAME",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.DEPTNO",
"type" : "number",
"o:length" : 2
},
{
"o:path" : "$.HIREDATE",
"type" : "string",
"o:length" : 32
}
]
Format the Output
Mostly, JSON_SERIALIZE function can be used to convert your JSON document which is held any supported data type to text. Besides, you can use this function in order to format your data. The PRETTY keyword displays the output more readable format.
SELECT
JSON_SERIALIZE('[{"EMPNO":"7839","ENAME":"KING"},{"EMPNO":"7698","ENAME":"BLAKE"},{"EMPNO":"7782","ENAME":"CLARK"}]' PRETTY)
FROM DUAL;
[
{
"EMPNO" : "7839",
"ENAME" : "KING"
},
{
"EMPNO" : "7698",
"ENAME" : "BLAKE"
},
{
"EMPNO" : "7782",
"ENAME" : "CLARK"
}
]
Furthermore, you can specify the returning output of the data type.
SELECT
JSON_SERIALIZE(CLOB_COLUMN RETURNING CLOB PRETTY)
FROM T_TAB;
Parsing JSON Data
In order to parse JSON data I will use JSON_TABLE function. For my examples, I will use the following scripts.
CREATE TABLE T_JSON_DOC
(
ID RAW(16) PRIMARY KEY,
DATA CLOB,
CONSTRAINT JSON_DOC_CHK CHECK (DATA IS JSON)
);
INSERT INTO T_JSON_DOC
VALUES(SYS_GUID(),
'{
"brand":"BMW",
"model":"4.20d",
"year":"2015",
"country":"Germany",
"price":"15000",
"engine":{
"number_of_cylinders":"8 cylinder",
"fuel_system":"4BBL carburetor"
}
}');
INSERT INTO T_JSON_DOC
VALUES(SYS_GUID(),
'{
"brand":"BMW",
"model":"5.30d",
"year":"2016",
"country":"Germany",
"price":"25000",
"engine":{
"number_of_cylinders":"4 cylinder",
"fuel_system":"fuel injected"
}
}');
INSERT INTO T_JSON_DOC
VALUES(SYS_GUID(),
'{
"brand":"Mercedes",
"model":"slk200",
"year":"2005",
"country":"Germany",
"price":"10000",
"engine":{
"number_of_cylinders":"4 cylinder",
"fuel_system":"4BBL carburetor"
}
}');
INSERT INTO T_JSON_DOC
VALUES(SYS_GUID(),
'{
"brand":"FORD",
"model":"Focus",
"year":"2013",
"country":"USA",
"price":"8000",
"engine":{
"number_of_cylinders":"8 cylinder",
"fuel_system":"fuel injected"
}
}');
INSERT INTO T_JSON_DOC
VALUES(SYS_GUID(),
'{
"brand":"Porche",
"model":"Carrera",
"year":"2016",
"country":"Germany",
"price":"12000",
"engine":{
"number_of_cylinders":"8 cylinder",
"fuel_system":"4BBL carburetor"
}
}');
COMMIT;
Example of JSON_TABLE with dot notation.
SELECT
t.*
FROM T_JSON_DOC,
JSON_TABLE(data, '$'
COLUMNS (brand VARCHAR2(50 CHAR) PATH '$.brand',
model VARCHAR2(50 CHAR) PATH '$.model',
year NUMBER PATH '$.year',
country VARCHAR2(50 CHAR) PATH '$.country',
price VARCHAR2(20 CHAR) PATH '$.price',
number_of_cylinders VARCHAR2(20 CHAR) PATH '$.engine.number_of_cylinders',
fuel_system VARCHAR2(20 CHAR) PATH '$.engine.fuel_system'
)
) t;
With the version of Oracle 18c, we can use the path expressions instead of dot notation as shown below.
SELECT
t.*
FROM T_JSON_DOC,
JSON_TABLE(data, '$'
COLUMNS (brand VARCHAR2(50 CHAR) PATH brand,
model VARCHAR2(50 CHAR) PATH model,
year NUMBER PATH year,
country VARCHAR2(50 CHAR) PATH country,
price VARCHAR2(20 CHAR) PATH price,
number_of_cylinders VARCHAR2(20 CHAR) PATH engine.number_of_cylinders,
fuel_system VARCHAR2(20 CHAR) PATH engine.fuel_system
)
) t;
Moreover, you don’t need any function to parse the JSON data. With using dot notation, you can easily use it as a predicate as shown below.
SQL> SET LINESIZE 200
SQL> COLUMN brand FORMAT A15
SQL> COLUMN model FORMAT A15
SQL> COLUMN year FORMAT A15
SQL> COLUMN country FORMAT A15
SQL> COLUMN price FORMAT A15
SQL>
SQL> SELECT
2 t.data.brand,
3 t.data.model,
4 t.data.year,
5 t.data.country,
6 t.data.price
7 FROM T_JSON_DOC t
8 where t.data.brand = 'Porche';
BRAND MODEL YEAR COUNTRY PRICE
--------------- --------------- --------------- --------------- ---------------
Porche Carrera 2016 Germany 12000
SQL> SELECT
2 t.data.brand,
3 t.data.model,
4 t.data.country,
5 t.data.price
6 FROM T_JSON_DOC t
7 where t.data.price > 10000;
BRAND MODEL COUNTRY PRICE
--------------- --------------- --------------- ---------------
BMW 4.20d Germany 15000
BMW 5.30d Germany 25000
Porche Carrera Germany 12000
How to convert XML to JSON
WITH
XML_TAB as (
select
XMLTYPE.createXML('<?xml version="1.0" encoding="UTF-8"?>
<cars>
<car>
<brand>BMW</brand>
<model>4.20d</model>
<year>2015</year>
<country>Germany</country>
<price>15000</price>
<engine>
<number_of_cylinders>8 cylinder</number_of_cylinders>
<fuel_system>4BBL carburetor</fuel_system>
</engine>
</car>
<car>
<brand>BMW</brand>
<model>5.30d</model>
<year>2016</year>
<country>Germany</country>
<price>25000</price>
<engine>
<number_of_cylinders>4 cylinder</number_of_cylinders>
<fuel_system>fuel injected</fuel_system>
</engine>
</car>
<car>
<brand>Mercedes</brand>
<model>slk200</model>
<year>2005</year>
<country>Germany</country>
<price>10000</price>
<engine>
<number_of_cylinders>4 cylinder</number_of_cylinders>
<fuel_system>4BBL carburetor</fuel_system>
</engine>
</car>
<car>
<brand>FORD</brand>
<model>Focus</model>
<year>2013</year>
<country>USA</country>
<price>8000</price>
<engine>
<number_of_cylinders>8 cylinder</number_of_cylinders>
<fuel_system>fuel injected</fuel_system>
</engine>
</car>
<car>
<brand>Porche</brand>
<model>Carrera</model>
<year>2016</year>
<country>Germany</country>
<price>12000</price>
<engine>
<number_of_cylinders>8 cylinder</number_of_cylinders>
<fuel_system>4BBL carburetor</fuel_system>
</engine>
</car>
</cars>') XML_COL from dual
)
SELECT
JSON_ARRAYAGG(
JSON_OBJECT(KEY 'car-'||to_char(rownum) VALUE
JSON_OBJECT(
KEY 'brand' VALUE t."brand",
KEY 'model' VALUE t."model",
KEY 'year' VALUE t."year",
KEY 'country' VALUE t."country",
KEY 'price' VALUE t."price",
KEY 'engine' VALUE
JSON_OBJECT(
KEY 'number_of_cylinders' VALUE t."number_of_cylinders",
KEY 'fuel_system' VALUE t."fuel_system"
)
)
)
)
FROM (
SELECT
a.*
FROM XML_TAB, XMLTABLE('/cars/*' passing XML_TAB.XML_COL
columns
"brand" path 'brand'
,"model" path 'model'
,"year" path 'year'
,"country" path 'country'
,"price" path 'price'
,"number_of_cylinders" path 'engine/number_of_cylinders'
,"fuel_system" path 'engine/fuel_system'
) a
) t;
[
{
"car-1":{
"brand":"BMW",
"model":"4.20d",
"year":"2015",
"country":"Germany",
"price":"15000",
"engine":{
"number_of_cylinders":"8 cylinder",
"fuel_system":"4BBL carburetor"
}
}
},
{
"car-2":{
"brand":"BMW",
"model":"5.30d",
"year":"2016",
"country":"Germany",
"price":"25000",
"engine":{
"number_of_cylinders":"4 cylinder",
"fuel_system":"fuel injected"
}
}
},
{
"car-3":{
"brand":"Mercedes",
"model":"slk200",
"year":"2005",
"country":"Germany",
"price":"10000",
"engine":{
"number_of_cylinders":"4 cylinder",
"fuel_system":"4BBL carburetor"
}
}
},
{
"car-4":{
"brand":"FORD",
"model":"Focus",
"year":"2013",
"country":"USA",
"price":"8000",
"engine":{
"number_of_cylinders":"8 cylinder",
"fuel_system":"fuel injected"
}
}
},
{
"car-5":{
"brand":"Porche",
"model":"Carrera",
"year":"2016",
"country":"Germany",
"price":"12000",
"engine":{
"number_of_cylinders":"8 cylinder",
"fuel_system":"4BBL carburetor"
}
}
}
]
You can also convert JSON data to XML with using DBMS_XMLGEN.GETXMLTYPE function.
SELECT
DBMS_XMLGEN.GETXMLTYPE
(q'[
SELECT
t.*
FROM JSON_TABLE('{
"brand":"Porche",
"model":"Carrera",
"year":"2016",
"country":"Germany",
"price":"12000",
"engine":{
"number_of_cylinders":"8 cylinder",
"fuel_system":"4BBL carburetor"
}
}', '$'
COLUMNS (brand VARCHAR2(50 CHAR) PATH '$.brand',
model VARCHAR2(50 CHAR) PATH '$.model',
year NUMBER PATH '$.year',
country VARCHAR2(50 CHAR) PATH '$.country',
price VARCHAR2(20 CHAR) PATH '$.price',
number_of_cylinders VARCHAR2(20 CHAR) PATH '$.engine.number_of_cylinders',
fuel_system VARCHAR2(20 CHAR) PATH '$.engine.fuel_system'
)
) t
]')
FROM DUAL;
<ROWSET>
<ROW>
<BRAND>Porche</BRAND>
<MODEL>Carrera</MODEL>
<YEAR>2016</YEAR>
<COUNTRY>Germany</COUNTRY>
<PRICE>12000</PRICE>
<NUMBER_OF_CYLINDERS>8 cylinder</NUMBER_OF_CYLINDERS>
<FUEL_SYSTEM>4BBL carburetor</FUEL_SYSTEM>
</ROW>
</ROWSET>
I hope this will help you 🙂
Brilliant. It hit the dry spot 🙂