How to build a JSON Array from the results of a SQL Query in Oracle ?
When developing a web application, you will often find the need to render a web page based on a JSON. In this post we will see how to generate a JSON in oracle that is greater than 32k characters. This post is mainly for people using earlier version of oracle database which do not provide any JSON functions.
with emp_data as (
select
emp_id,
emp_dept
from employees
order by emp_id ),
emp_json_object AS (SELECT '{ ' || '"EMP_ID" : "' || emp_id || '", ' ||
'"EMP_DEPT" : "' || emp_dept || '"' ||
'}' AS json_objects
FROM emp_data)
SELECT
RTRIM(
XMLCAST(
XMLAGG(XMLELEMENT (E, json_objects || ',')) -- use a comma between JSON objects/rows of data
AS CLOB), -- cast the xml to a clob
',') -- rtrim the last comma away
INTO res_json --CLOB
FROM emp_json_object;
When developing a web application, you will often find the need to render a web page based on a JSON. In this post we will see how to generate a JSON in oracle that is greater than 32k characters. This post is mainly for people using earlier version of oracle database which do not provide any JSON functions.
Here I am using the below 3 XML functions in oracle :
1)
XMLELEMENT
2)
XMLAGG
3)
XMLCAST
XMLELEMENT
The
XMLElement function is
typically nested to produce an XML document with a nested structure. It
creates an XML element from the parameters passed to it.
Refer the documentation for more details on its usage.
In our code , we use this function to create an XML element out of each row
from the results of a SQL Query.
XMLAGG
XMLAGG is an aggregate function. It takes a collection of
XML fragments and returns an aggregated XML document. Refer the documentation
for XMLAGG for more details on its usage.In our code we
will use this function to aggregate each XML Element generated by using the XMLELEMENT
function on each row in the SQL result.
XMLCAST
XMLCast casts the argument passed to the scalar SQL data type specified.
Refer the documentation for XMLCAST for more details on its usage. In our code we
will use this function to cast the result into a CLOB object.
Below is the sample code to use these XML functions and
generate a JSON Array from the results of a SQL query . Append the result with square brackets '[' and ']' in the beginning and end to make it a valid JSON array .
with emp_data as (
select
emp_id,
emp_dept
from employees
order by emp_id ),
emp_json_object AS (SELECT '{ ' || '"EMP_ID" : "' || emp_id || '", ' ||
'"EMP_DEPT" : "' || emp_dept || '"' ||
'}' AS json_objects
FROM emp_data)
SELECT
RTRIM(
XMLCAST(
XMLAGG(XMLELEMENT (E, json_objects || ',')) -- use a comma between JSON objects/rows of data
AS CLOB), -- cast the xml to a clob
',') -- rtrim the last comma away
INTO res_json --CLOB
FROM emp_json_object;
No comments :
Post a Comment