Generating JSON from SQL cursor

It appears there will be a good 1001 uses for the APEX_JSON package in APEX 5, here is one of them.

Previously I had an AJAX PL/SQL callback that returned a relatively simple JSON string, using the LISTAGG technique described by Lucas here.

declare
  l_json varchar2(32767);
begin
  select listagg('{"id":"'||version||'","content":"'||version||'.x"}',',')  within group (order by null) json
  into l_json
  from (
    select distinct substr(version,1,1) version
    from  apex_timeline
    order by version
  );

  sys.htp.prn('['||l_json||']');
end getGroups;

Now it can be further simplified by taking LISTAGG out of the equation, which can be a problem for larger data sets:

DECLARE
  c sys_refcursor;
BEGIN
  open c for 
    select version    as id
       ,version||'.x' as content
  from (
    select distinct substr(version,1,1) version
    from  apex_timeline
    order by version
  );

  apex_json.write(c);
END;

The output differs only slightly
[{"id":"1","content":"1.x"},{"id":"2","content":"2.x"},{"id":"3","content":"3.x"},{"id":"4","content":"4.x"},{"id":"5","content":"5.x"}]
[ { "ID":"1" ,"CONTENT":"1.x" } ,{ "ID":"2" ,"CONTENT":"2.x" } ,{ "ID":"3" ,"CONTENT":"3.x" } ,{ "ID":"4" ,"CONTENT":"4.x" } ,{ "ID":"5" ,"CONTENT":"5.x" } ]

A few extra spaces in the apex_json version. If lower case required for JSON attributes then use double quotes around column aliases, ie: select version as "id"

Oracle 12c JSON APIs seem to be all about validating and deconstruction, while this API has a bunch of overloaded apex_json.write() modules, including support for CLOBs.

Craig Sykes demonstrated how this could be done dynamically with a simple page.