Easier, Better, Faster JSON: 12 Things Developers Will Love About Oracle Database 12c Release 2 Part 1

12.1.0.2 brought JSON support to Oracle Database. This helped you work with JSON documents stored in clobs or varchar2s.

These are fantastic. But storing raw JSON should be the exception, not the norm. Most of the time you should shred your JSON documents into relational tables.

This leaves you with a problem though. Getting the data back out in JSON format!

Trying to write your own JSON generator is hard. So in 12.2 we offer a whole host of options to help you get the job done.

JSON From SQL

12.2 provides four key functions to help you write SQL that returns data in JSON format:

• JSON_object
• JSON_objectagg
• JSON_array
• JSON_arrayagg

You use the JSON_object* functions to create series of key-value pair documents. i.e. the output has curly braces {}. The JSON_array* 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.

OK. So how do these work?

Let’s look at an example.

Say you’re using the classic employees and departments tables. For each department you want a JSON document that contains:

• The department name
• An array of its employees
• Each element of this array should be its own document, listing the employee’s name and their job title.

For example:

{ 
  "department": "Accounting", 
  "employees": [ { 
    "name": "Shelley,Higgins", 
    "job": "Accounting Manager" 
  }, { 
    "name": "William,Gietz", 
    "job": "Public Accountant" 
  } ] 
}

How do you create this using the new functions?

Let’s work from the inside out:

• First you need a document for each employee. This has two attributes, name and job. 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 department to split out the employees for each one into a separate array.
• Finally you have a document per department. So you need another JSON_object with department and employees attributes. The values for these are the department name and the results of the JSON_arrayagg call in the previous step.

Put it all together and you get:

select json_object (
  'department' value d.department_name, 
  'employees' value json_arrayagg ( 
    json_object ( 
      'name' value first_name || ',' || last_name, 
      'job' value job_title 
    )
  )
) 
from  hr.departments d, hr.employees e, hr.jobs j 
where d.department_id = e.department_id 
and   e.job_id = j.job_id 
group by d.department_name;

And voila! You have your JSON!

JSON in PL/SQL

So now you have your JSON document. But what if you want to edit it?

Say you want to change the names to uppercase. And add a title element. So the previous document becomes:

{ 
  "department": "Accounting", 
  "employees": [ { 
    "name": "SHELLEY,HIGGINS", 
    "job": "Accounting Manager", "title": "" 
  }, {
    "name": "WILLIAM,GIETZ", 
    "job": "Public Accountant", 
    "title": "" 
  } ] 
}

If you’re generating the document it’s easiest to add these in the SQL! So this assumes you want to change a JSON document from an external source.

To help with this, there are new PL/SQL objects. These enable you to access, modify and add elements to a JSON document with get/put calls.

The key object types are:

• json_element_t — a supertype for documents and arrays
• json_object_t — for working with JSON documents
• json_array_t — for working with JSON arrays

The first thing you need to do is create the JSON object. Do this by parsing the document:

doc := json_object_t.parse(' {
  "department": "Accounting", 
  "employees": [ { 
    "name": "Shelley,Higgins", 
    "job": "Accounting Manager" 
  }, { 
    "name": "William,Gietz", 
    "job": "Public Accountant" } ] 
  } '
);

You can then access the employees array using get:

emps := treat(doc.get('employees') as json_array_t) ;

The treat function casts the element to the appropriate type (JSON_array_t here).

Once you have the array, you can loop through the employees. Put adds a new key if it’s not present. Otherwise it overwrites the existing value.

for i in 0 .. emps.get_size - 1 loop 
  emp := treat(emps.get(i) as json_object_t); 
  emp.put('title', ''); 
  emp.put('name', upper(emp.get_String('name'))); 
end loop;

The get functions return a reference to the original object. So if you get some JSON and modify it, the original document also changes!

If you don’t want this, clone the element when you get it. For example:

emps := treat(doc.get('employees') as json_array_t).clone

So the complete PL/SQL block to transform the JSON is:

declare 
  doc json_object_t; 
  emps json_array_t; 
  emp json_object_t; 
begin 
  doc := json_object_t.parse('{ 
      "department": "Accounting", 
      "employees": [ { 
        "name": "Shelley,Higgins", 
        "job": "Accounting Manager" 
      }, { 
        "name": "William,Gietz", 
        "job": "Public Accountant" 
      } ] 
    }'
  ); 
  emps := treat(doc.get('employees') as json_array_t) ; 
  for i in 0 .. emps.get_size - 1 loop 
    emp := treat(emps.get(i) as json_object_t); 
    emp.put('title', ''); 
    emp.put('name', upper(emp.get_String('name'))); 
  end loop;   
  dbms_output.put_line(doc.to_String); 
end; 
/
{ 
  "department": "Accounting", 
  "employees": [ { 
    "name": "SHELLEY,HIGGINS", 
    "job": "Accounting Manager", 
    "title": "" 
  }, { 
    "name": "WILLIAM,GIETZ", 
    "job": "Public Accountant", 
    "title": "" 
  } ] 
}

Now you can generate JSON from SQL and change it in PL/SQL you have powerful options to work with it.

And there’s a raft of other improvements to JSON functionality in 12.2. Other enhancements include:

• JSON_exists function
• Support for In-Memory, Partitioning and Materialized Views
• Search indexes
• GeoJSON
• JSON Data Guide

If you’re desperate to work with JSON, I recommend checking these out.

Full article originally published at blogs.oracle.com on November 10, 2016.