JSON For Absolute Beginners: Part 5 – Generating JSON in Oracle

In this fifth article in our series on JSON and the Oracle database we’ll be pulling the curtains back on the magic tricks that can convert your boring old relational data to JSON. OK, maybe that’s overselling it a little. Obviously there’s no magic involved; indeed some may say it’s anything but. But before we get to all of that,

In this fifth article in our series on JSON and the Oracle database we’ll be pulling the curtains back on the magic tricks that can convert your boring old relational data to JSON. OK, maybe that’s overselling it a little. Obviously there’s no magic involved; indeed some may say it’s anything but.

But before we get to all of that, if you’re new to this whole JSON malarkey, you’ll probably want to start from Article 1 in this series and work your way back up to this point. Go on, we’ll wait for you.

As I’ve intimated throughout the series, JSON – compared to, say, XML – is treated a little like the dark-haired stepchild in Oracle. And nowhere is this more apparent than when it comes to generating JSON. You probably assume that there are JSON translations for those XML stables like XMLElement, XMLForest and the rest, right?

Wrong. If – in 2016 – you want to generate JSON from your relational data in Oracle, here’s how you have to do it.

Building JSON Manually

You could, of course, roll up your sleeves and construct your JSON string manually. After all, it’s just text, and so someone with your sql and pl/sql ninja skills should be able to knock it together with minimum fuss. OK, maybe not minimum fuss, but it is possible.

The following select statement uses a with clause and analytic functions to transform the contents of the Dept and Emp tables into JSON data.

 WITH depts AS (SELECT '{ '
                     ||' "Department":"'||dname ||'"'
                     ||',"ID":"'        ||deptno||'"'
                     ||',"City":"'      ||loc   ||'"'
                     ||',"Staff":'      ||(  SELECT '['
                                               ||LISTAGG('{' ||'"Name":"' ||ename|| '"'|| ',"Job":"'|| job|| '"'||',"Salary":'||sal||'}', ',') WITHIN GROUP (ORDER BY 1)
                                              FROM emp 
                                              WHERE emp.deptno = dept.deptno)
                    ||'}' AS dept_detail
               FROM dept)
SELECT '{"Organisation Structure" : [ '
    ||(SELECT LISTAGG( dept_detail, ',') WITHIN GROUP (ORDER BY 1)
       FROM depts)
    ||']}' organisation_json

So yes, it is possible. But did you know that it’s also possible to survive for a full month eating nothing but raw potatoes? I read it on the Internet so it must be true. But just because a thing is possible doesn’t mean it’s a good idea or even remotely practical. So no, you don’t want to handcraft all your JSON data. Life’s too short.


When Apex 5.0 was released back in 2015, one of the new packages was APEX_JSON. The package contains, amongst other goodies, procedures that allow you build or generate valid JSON. Here are a few of the routines that I think you should know:

OPEN_OBJECT – writes an open curly bracket.
OPEN_ARRAY – writes an open square bracket
WRITE – overloaded procedure that writes a value.
CLOSE_ARRAY – writes a close square bracket
CLOSE_OBJECT – writes a close curly bracket
CLOSE_ALL – closes all open objects and arrays
INITIALIZE_CLOB_OUTPUT – instructs the package to write all output to a temporary clob
GET_CLOB_OUTPUT – returns the contents of the temporary clob
FREE_OUTPUT – call this after get_clob_output to free the output resources.

You can probably guess how these procedures work and how to use them, but since you’re here and I’m here I might as well patronise you with two examples – one in which we construct the JSON string manually, and a second using a weak ref cursor. Let’s return to the dept and emp tables.

 APEX_JSON.OPEN_OBJECT('Organisation Structure');
           FROM dept) LOOP


          FOR j IN (SELECT *
                    FROM emp
                    WHERE emp.deptno = i.deptno) LOOP

          END LOOP;


Or, if you wanted to produce the same output a little less manually, you could use a ref cursor.

  OPEN l_cur FOR
    SELECT dname "Department",
           deptno "ID",
           loc "City",
           CURSOR(SELECT ename "Name",
                         job "Job",
                         sal "Salary"
                  FROM   emp 
                  WHERE  emp.deptno = dept.deptno ) AS "Staff"
    FROM   dept 
    ORDER BY dname;


  APEX_JSON.WRITE('Organisation Structure', l_cur);


Depending on your preferences, where your SQL strengths lie and your particular requirement, either of these methods should work for you. It’s all as straightforward as peeling a banana. But…

Well, APEX_JSON comes bundled with Apex from version 5.0, so if you don’t have Application Express installed, or if, for whatever reason, you have decided not to upgrade to Release 5, then you’re out of luck. Which means you have to return to the Stone Ages and to chiselling your JSON data using crude tools like concatenation and analytical functions.

Unless there’s another way…


There is, and it’s called PL/JSON. PL/JSON is an open source suite of packages that, once installed, gives you the ability to generate JSON data. And while it might not match APEX_JSON when it comes to simplicity, it is not a million miles from it in the way it goes about solving problems.

It has two object types – JSON for objects and JSON_LIST for arrays – and a number of procedures that allow you manipulate your instances of these objects, once you’ve created them. Chief amongst those procedures is put, which unsurprisingly is the analog of APEX_JSON’s write procedure.

This is the point at which I would usually furnish you with an example, reproducing the JSON data from our previous sections using PL/JSON. However, Dan McGhan has pretty much done the work for me in a blogpost he has written so I might as well point you in that direction.

PL/JSON is quite popular and, until the advent of APEX_JSON, was probably the premier way of working with JSON in the Oracle database. It comes with a large number of example files, but no further documentation, and so you have to be ready to hack through the thicket of trial-and-error if you wish to learn it.


Oracle 12.2

But, you know what? None of that should matter for very long. And that is because Oracle 12c Release 2 (planned for late 2016) promises to fill the gap we’ve been talking about with a slate of new SQL/JSON functions for generating JSON data. The functions – json_object, json_array, json_objectagg, and json_arrayagg – they say, will be easy to use, and lightweight.

All of which means that in a few short months, I might have to rewrite this whole article. But, you know what, if the new functionality lives up to its promise, I’ll do it with a smile on my face.