As I\u2019ve 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?<\/p>\n
Wrong. If – in 2016 – you want to generate JSON from your relational data in Oracle, here\u2019s how you have to do it.<\/p>\n
Building JSON Manually<\/h5>\n
You could, of course, roll up your sleeves and construct your JSON string manually. After all, it\u2019s 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<\/em> fuss, but it is<\/em> possible.<\/p>\n The following select statement uses a with clause<\/a> and analytic functions<\/a> to transform the contents of the Dept and Emp tables into JSON data.<\/p>\n So yes, it is possible. But did you know that it\u2019s 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\u2019t mean it\u2019s a good idea or even remotely practical. So no, you don\u2019t want to handcraft all your JSON data. Life\u2019s too short.<\/p>\n When Apex 5.0 was released back in 2015, one of the new packages was APEX_JSON<\/a>. 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:<\/p>\n OPEN_OBJECT<\/strong> – writes an open curly bracket. You can probably guess how these procedures work and how to use them, but since you\u2019re here and I\u2019m 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\u2019s return to the dept and emp tables.<\/p>\n Or, if you wanted to produce the same output a little less manually, you could use a ref cursor.<\/p>\n Depending on your preferences, where your SQL strengths lie and your particular requirement, either of these methods should work for you. It\u2019s all as straightforward as peeling a banana. But\u2026<\/p>\n Well, APEX_JSON comes bundled with Apex from version 5.0, so if you don\u2019t have Application Express installed, or if, for whatever reason, you have decided not to upgrade to Release 5, then you\u2019re 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.<\/p>\n Unless there\u2019s another way\u2026<\/p>\n There is, and it\u2019s called PL\/JSON<\/a>. 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.<\/p>\n It has two object types – JSON <\/em>for objects and JSON_LIST <\/em>for arrays – and a number of procedures that allow you manipulate your instances of these objects, once you\u2019ve created them. Chief amongst those procedures is put<\/em>, which unsurprisingly is the analog of APEX_JSON\u2019s write <\/em>procedure.<\/p>\n 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<\/a> he has written so I might as well point you in that direction.<\/p>\n 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.<\/p>\n Ouch.<\/p>\n WITH depts AS (SELECT '{ '\r\n ||' \"Department\":\"'||dname ||'\"'\r\n ||',\"ID\":\"' ||deptno||'\"'\r\n ||',\"City\":\"' ||loc ||'\"'\r\n ||',\"Staff\":' ||( SELECT '['\r\n ||LISTAGG('{' ||'\"Name\":\"' ||ename|| '\"'|| ',\"Job\":\"'|| job|| '\"'||',\"Salary\":'||sal||'}', ',') WITHIN GROUP (ORDER BY 1)\r\n ||']' \r\n FROM emp \r\n WHERE emp.deptno = dept.deptno)\r\n ||'}' AS dept_detail\r\n FROM dept)\r\nSELECT '{\"Organisation Structure\" : [ '\r\n ||(SELECT LISTAGG( dept_detail, ',') WITHIN GROUP (ORDER BY 1)\r\n FROM depts)\r\n ||']}' organisation_json\r\nFROM DUAL ;\r\n<\/pre>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/10\/organisation_structure_sql.jpg\")
<\/p>\nAPEX_JSON<\/h5>\n
\n OPEN_ARRAY<\/strong> – writes an open square bracket
\n WRITE <\/strong>– overloaded procedure that writes a value.
\n CLOSE_ARRAY <\/strong>– writes a close square bracket
\n CLOSE_OBJECT <\/strong>– writes a close curly bracket
\n CLOSE_ALL <\/strong>– closes all open objects and arrays
\n INITIALIZE_CLOB_OUTPUT <\/strong>– instructs the package to write all output to a temporary clob
\n GET_CLOB_OUTPUT <\/strong>– returns the contents of the temporary clob
\n FREE_OUTPUT <\/strong>– call this after get_clob_output to free the output resources.<\/p>\nBEGIN\r\n \r\n APEX_JSON.INITIALIZE_CLOB_OUTPUT;\r\n APEX_JSON.OPEN_OBJECT;\r\n APEX_JSON.OPEN_OBJECT('Organisation Structure');\r\n APEX_JSON.OPEN_ARRAY;\r\n FOR i IN (SELECT *\r\n FROM dept) LOOP\r\n\r\n APEX_JSON.OPEN_OBJECT;\r\n APEX_JSON.WRITE('Department',i.dname);\r\n APEX_JSON.WRITE('ID',i.deptno);\r\n APEX_JSON.WRITE('City',i.loc);\r\n APEX_JSON.OPEN_ARRAY('Staff');\r\n\r\n FOR j IN (SELECT *\r\n FROM emp\r\n WHERE emp.deptno = i.deptno) LOOP\r\n\r\n APEX_JSON.OPEN_OBJECT;\r\n APEX_JSON.WRITE('Name',j.ename);\r\n APEX_JSON.WRITE('Job',j.job);\r\n APEX_JSON.WRITE('Salary',j.sal);\r\n APEX_JSON.CLOSE_OBJECT;\r\n END LOOP;\r\n APEX_JSON.CLOSE_ARRAY;\r\n APEX_JSON.CLOSE_OBJECT;\r\n END LOOP;\r\n APEX_JSON.CLOSE_ALL;\r\n\r\n DBMS_OUTPUT.PUT_LINE(APEX_JSON.GET_CLOB_OUTPUT);\r\n APEX_JSON.FREE_OUTPUT;\r\nEND;\r\n\/\r\n<\/pre>\nDECLARE\r\n l_cur SYS_REFCURSOR;\r\nBEGIN\r\n \r\n OPEN l_cur FOR\r\n SELECT dname \"Department\",\r\n deptno \"ID\",\r\n loc \"City\",\r\n CURSOR(SELECT ename \"Name\",\r\n job \"Job\",\r\n sal \"Salary\"\r\n FROM emp \r\n WHERE emp.deptno = dept.deptno ) AS \"Staff\"\r\n FROM dept \r\n ORDER BY dname;\r\n\r\n APEX_JSON.INITIALIZE_CLOB_OUTPUT;\r\n\r\n APEX_JSON.OPEN_OBJECT;\r\n APEX_JSON.WRITE('Organisation Structure', l_cur);\r\n APEX_JSON.CLOSE_OBJECT;\r\n\r\n DBMS_OUTPUT.PUT_LINE(APEX_JSON.GET_CLOB_OUTPUT);\r\n APEX_JSON.FREE_OUTPUT;\r\nEND;\r\n\/\r\n<\/pre>\nPL\/JSON<\/h5>\n
Oracle 12.2<\/h5>\n