Creating JSON document straight from SQL query – using LISTAGG and With Clause

This very brief article demonstrates how a fairly complex, nested JSON document can be created using a single SQL query. The main features used in the SQL statement are the WITH clause to define the inline views per object (Department, Manager, Employee), Scalar Subquery to retrieve the result from an inline view as string into the overall JSON string and LISTAGG to collect multiple elements into a JSON list.

The article shows a very simple application of this JSON creation through a simple HTML page – served by a PL/SQL package. This page does not contain any data. Right after loading, the onLoad event triggers a listener that invokes – through AJAX – the PL/SQL package’s other procedure. This procedure returns the JSON object – courtesy of that fabulous SQL query – and uses it to write the company details on the fly into the page.

Image

SQL Query to create JSON string

The following SQL query creates JSON for a company object with all data from DEPT and EMP (SCOTT Schema) as its contents:

with manager as
( select '{ '
       ||' "name":"'||ename||'"'
       ||',"salary":'||sal
       ||',"hiredate":"'||to_char(hiredate, 'DD-MM-YYYY')||'"'
       ||'} ' json
  , emp.*
  from   emp
)
, employee as
( select '{ '
       ||' "name":"'||ename||'"'
       ||',"job":"'||job||'"'
       ||',"salary":'||sal
       ||',"manager":'||case when mgr is null then '""' else (select json from manager mgr where mgr.empno = emp.mgr) end       ||',"hiredate":"'||to_char(hiredate, 'DD-MM-YYYY')||'"'
       ||'} ' json
  , emp.*
  from   emp
)
, department as
( select '{ '
       ||' "name":"'||dname||'"'
       ||',"identifier":"'||deptno||'"'
       ||',"location":"'||loc||'"'
       ||',"employees":'||(  select '['||listagg( json, ',')
                                                  within group (order by 1)
                                  ||']' as data
                             from employee emp
                             where emp.deptno = dept.deptno
                          )
       ||'} ' json
  from   dept
)
select '{"company" : ['
       ||( select listagg( json, ',')
                  within group (order by 1)
           from   department
          )
       ||']}'
from   dual

The result looks like:

Image

When made prettier with http://jsonlint.com/ here is what it looks like:

Image

The PL/SQL package hrm_json renders the very simple HTML page – through its hrmpage procedure that is accessed from the browser using http://127.0.0.1:8080/hello/hrm_json.hrmpage

The page renders with an onLoad trigger and a bit of JavaScript

Image

that will invoke the company procedure on the hrm_json package.

Image

This procedure returns the JSON object with company data:

procedure company
as
  l_data   varchar2(4000);
begin
with manager as
( select '{ '
       ||' "name":"'||ename||'"'
       ||',"salary":'||sal
       ||',"hiredate":"'||to_char(hiredate, 'DD-MM-YYYY')||'"'
       ||'} ' json
  , emp.*
  from   emp
)
, employee as
( select '{ '
       ||' "name":"'||ename||'"'
       ||',"job":"'||job||'"'
       ||',"salary":'||sal
       ||',"manager":'||case when mgr is null then '""' else (select json from manager mgr where mgr.empno = emp.mgr) end       ||',"hiredate":"'||to_char(hiredate, 'DD-MM-YYYY')||'"'
       ||'} ' json
  , emp.*
  from   emp
)
, department as
( select '{ '
       ||' "name":"'||dname||'"'
       ||',"identifier":"'||deptno||'"'
       ||',"location":"'||loc||'"'
       ||',"employees":'||(  select '['||listagg( json, ',')
                                                  within group (order by 1)
                                  ||']' as data
                             from employee emp
                             where emp.deptno = dept.deptno
                          )
       ||'} ' json
  from   dept
)
select '{"company" : ['
       ||( select listagg( json, ',')
                  within group (order by 1)
           from   department
          )
       ||']}'
into l_data
from   dual
  ;
 htp.p(l_data);
end company;

The JavaScript function in the page that is invoked when the AJAX request returns with the JSON string holding the company data first evaluates the JSON snippet, turning it into a true JavaScript memory structure. Subsequently, a table is written on the fly and set to the innerHTML property of the DIV element with id hrmDetails.

Image

Resources

Download the code for the PL/SQL package hrm_json: simpleEPGwithJSONbasedTablet.