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.
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:
When made prettier with http://jsonlint.com/ here is what it looks like:
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
that will invoke the company procedure on the hrm_json package.
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.
Resources
Download the code for the PL/SQL package hrm_json: simpleEPGwithJSONbasedTablet.