What is the method for converting data (stored in the Oracle database) into JSON format? Multiple approaches exist for converting data from an Oracle database into JSON format. Depending on the desired outcome, SQL functions such as LISTAGG, CONCAT, or UNPIVOT can be used within a SELECT statement. For more complex JSON output requirements, specialized SQL/JSON functions are available to meet those needs. Following a conversation with a client, I made the decision to translate and refresh an earlier German article of mine on this subject and to re-publish it in English. Let’s examine several methods. You can use Oracle Database version19c or 23ai in the Cloud or on-premises to try out the following short tutorial.
1. Generation of JSON Data With SQL/JSON Functions
In the first example, the function JSON_OBJECT is used to generate values from the column content. We joined the EMP and DEPT table (download e.g. from LiveSQL). The result is a JSON object. Keep in mind a collection type cannot be passed to JSON_OBJECT. You can find the full syntax for JSON_OBJECT here. We incorporated JSON_SERIALIZE (refer to the documentation for more details) to achieve a more readable text presentation.
SQL> select JSON_OBJECT('emp_id' is e.empno, 'department' is d.dname) result
from scott.dept d join scott.emp e on d.deptno=e.deptno;
RESULT
--------------------------------------------------------------
{
"EMPNO" : 7839,
"ENAME" : "KING",
"JOB" : "PRESIDENT",
"MGR" : null,
"HIREDATE" : "1981-11-17T00:00:00",
"SAL" : 5000,
"COMM" : null,
"DEPTNO" : 10,
"DEPTNO" : 10,
"DNAME" : "ACCOUNTING",
"LOC" : "NEW YORK"
}
{
"EMPNO" : 7698,
"ENAME" : "BLAKE",
"JOB" : "MANAGER",
"MGR" : 7839,
"HIREDATE" : "1981-05-01T00:00:00",
...
In the next example, the output includes the column EMPNO (noted as EMP_ID) and DNAME, the associated department name (noted as department).
SQL> select JSON_OBJECT('emp_id' is e.empno, 'department' is d.dname) result
from scott.dept d join scott.emp e on d.deptno=e.deptno order by empno;
RESULT
--------------------------------------------------------------------------------
{"emp_id":7369,"department":"RESEARCH"}
{"emp_id":7499,"department":"SALES"}
{"emp_id":7521,"department":"SALES"}
{"emp_id":7566,"department":"RESEARCH"}
{"emp_id":7654,"department":"SALES"}
{"emp_id":7698,"department":"SALES"}
{"emp_id":7782,"department":"ACCOUNTING"}
{"emp_id":7788,"department":"RESEARCH"}
{"emp_id":7839,"department":"ACCOUNTING"}
{"emp_id":7844,"department":"SALES"}
{"emp_id":7876,"department":"RESEARCH"}
{"emp_id":7900,"department":"SALES"}
{"emp_id":7902,"department":"RESEARCH"}
{"emp_id":7934,"department":"ACCOUNTING"}
14 rows selected.
Now, the ENAME column from the EMP table should be displayed as JSON aggregate. JSON_OBJECTAGG function is an aggregate function that accepts a property key-value pair as input. You can find a detailed documentation here.
SQL> select d.deptno deptno, JSON_OBJECTAGG(KEY 'emp_name' VALUE ename) ename_per_deptno
from scott.dept d join scott.emp e on d.deptno=e.deptno group by d.deptno;
DEPTNO
----------
ENAME_PER_DEPTNO
------------------------------------------------------------------------------------------------
10
{"emp_name":"KING","emp_name":"MILLER","emp_name":"CLARK"}
20
{"emp_name":"JONES","emp_name":"ADAMS","emp_name":"SMITH","emp_name":"FORD","emp_name":"SCOTT"}
30
{"emp_name":"BLAKE","emp_name":"JAMES","emp_name":"TURNER","emp_name":"MARTIN","
emp_name":"WARD","emp_name":"ALLEN"}
2. With Oracle SQL Developer linemode tool SQLcl
If you have no special requirements in terms of output, you can simply use the Oracle SQL Developer Command Line tool SQLcl. SQLcl combines the advantages of a linemode tool with the features of SQL Developer and can be loaded and installed separately from OTN, just like SQL Developer.
The following example shows the syntax of the SQLcl command SET SQLFORMAT, which can be used to generate a JSON output. I used SQLcl in Oracle Cloud with the option oci. For more information how to connect with SQLcl refer to the User’s Guide.
[oracle@de admin]$ sql -oci
SQLcl: Release 24.4 Production on Mon Mar 31 11:48:15 2025
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Username? (''?) scott@US
Password? (**********?) **************
Last Successful login time: Mon Mar 31 2025 11:48:34 +00:00
Connected to:
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0
SQL> help set sqlformat
SET SQLFORMAT
SET SQLFORMAT { default,csv,text,html,xml,json,fixed,insert,loader,delimited,ansiconsole}
default : SQL*PLUS style formatting
csv : comma separated and string enclosed with "
text : tab separated with customizable left and right enclosures (default enclosure is ")
set sqlformat text [left enclosure] [right enclosure]
Example:
set sqlformat text < >
7369 <SMITH> <CLERK> 7902 17-DEC-80 800 20,5555555555554444
html : html tabular format
xml : xml format of /results/rows/column/*
json : json format matching ORDS Collection Format
json-formatted : json format matching ORDS Collection Format and pretty printed
...
SQL> set sqlformat JSON
SQL> select * from dept;
{"results":[{"columns":[{"name":"DEPTNO","type":"NUMBER"},{"name":"DNAME","type":"VARCHAR2"},{"name":"LOC","type":"VARCHAR2"}],"items":
[
{"deptno":10,"dname":"ACCOUNTING","loc":"NEW YORK"}
,{"deptno":20,"dname":"RESEARCH","loc":"DALLAS"}
,{"deptno":30,"dname":"SALES","loc":"CHICAGO"}
,{"deptno":40,"dname":"OPERATIONS","loc":"BOSTON"}
]}]}
3. XML format
If the column is in XML format, you may use the XMLTOJSON function to convert XML data into JSON.
SQL> set sqlformat default
SQL Format Cleared
SQL> select XMLTOJSON(XMLType('<person><name>Martin</name><id>1234</id></person>')) result
from dual;
RESULT
--------------------------------------
{"person":{"id":1234,"name":"Martin"}}
SQL> create table x_tab (xmldoc xmltype);
Table created.
SQL> insert into x_tab values (xmltype('<?xml version="1.0" encoding="UTF-8"?> <note> <to>Jim</to> <from>Jani</from> <topic>Reminder</topic> <detail>Meeting on Monday</detail> </note>'));
1 row created.
SQL> select xmltojson(xmldoc) result from x_tab;
RESULT
-----------------------------------------------------------------------------------
{"note":{"detail":"Meeting on Monday","from":"Jani","to":"Jim","topic":"Reminder"}}
More information on the SQL/JSON functions can be found in the SQL Language Guide or in the JSON Developer’s Guide.