Easier, Better, Faster JSON in ORACLE 12c Release 2

Oracle Database supports for JSON from their version of 12.1.0.2. It is managing JSON documents stored in CLOBs or VARCHAR2s. Most of the time it is easy to process data if you store your JSON data in relational tables. But it's become hard again if you want to retrieve your data in JSON format. Then the option is to implement your own JSON generator which is not easy. 

So in 12.2 Oracle offers a whole host of options to help you get the job done. Now you can generate the JSON from SQL. Following new functions will help you to write SQL that returns relational table data in JSON format.

• JSON_object
• JSON_objectagg
• JSON_array
• JSON_arrayagg

JSON_object

Use to create series of key-value pair documents. i.e the output has curly braces {}.

JSON_array

These functions take a list of values and return it as an array. i.e in square brackets []. 

For each row in the input, the non-agg versions of these functions output a row. The agg versions combine multiple rows into a single document or array. Let's look at an example.

Say you're using the classic students and courses tables. For each department, you want a JSON document that contains:

• The course name
• An array of its students

• Each element of this array should be its own document, listing the student's name and their contact number. 

For example:

{ 

"course": "programming", 

"students": [   

  {     

     "name": "Dimuthu,Rathnayake",

      "contact": "0716586402"

  },   

  {     

      "name": "Kalpa,Wijerathne",

      "contact": "0765932762" 

   } 

  ]

}

How do you create this using the new functions? Let's work from the inside out:

• First, you need a document for each student. This has two attributes, name and contact. Pass these into a JSON_object call.
• Then you need to turn these into an array. So wrap the JSON_object in a JSON_arrayagg. Group by the course to split out the students for each one into a separate array.
• Finally, you have a document per course. So you need another JSON_object with a course and students attributes. The values for these are the course name and the results of the JSON_arrayagg call in the previous step.

Put it all together and you get:

select json_object (

          'course' value c.course_name,

           'students' value json_arrayagg (

              json_object (

                'name' value first_name || ',' ||last_name,

                'contact' value contact_no )))

from uni.course c, uni.students s, uni.contacts con

where c.course_id = s.course_id

and s.contact_id = con.contact_id

group by c.course_name;

You will get the above JSON output