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.

(more…)