Generating JSON data

Previous entries in this blog have shown how you can store and query existing JSON data. Since Oracle Database release 12.2 we allow the creation of JSON data using 4 new SQL operators  JSON_OBJECT, JSON_OBJECTAGG, JSON_ARRAY, JSON_ARRAYAGG (which are all part of the new SQL 2016 standard). In this entry we will walk you thru the 4 operators and show you how a combination can create a complex JSON instance.

But first the use cases; why would someone want to create JSON from (relational) data in the Oracle database?

A typical use case expressed by customers is to web-enable a (legacy) relational application. In this case the data is stored relationally (aka in tables and columns) and a new browser-based application benefits if the data coming from the database is in JSON format. Instead of relying on some middleware code to turn the data into JSON wouldn't it be nicer (and faster) if the database could perform the conversion?

Let's take the following as as example: an very simple model of a purchasing system. Customers can submit orders and orders consists of one or multiple items from a catalog.

This is the Entity-Relationship-Diagram which shows the attributes with a few attributes: 

The following tables are used to reflect this ER-diagram; for the sake of simplicity I omit primary/foreign keys relationships.

CREATE TABLE customers (
    cust_id number,    
    first varchar2(30),  
    last varchar2(30),
    join_date timestamp);

insert into customers values (1, 'Eric', 'Cartman', SYSDATE);
insert into customers values (2, 'Kenny', 'McCormick', SYSDATE);
insert into customers values (3, 'Kyle', 'Brofloski', SYSDATE);
insert into customers values (4, 'Stan', 'Marsh', SYSDATE);

CREATE TABLE items (
    item_id number, 
    name    varchar2(30),  
    price   number(5,2),
    stock_quantity number);

insert into items values (122, 'Potato Gun', 29.99, 10);
insert into items values (232, 'Rubber Christmas Tree', 65.00, 0);
insert into items values (345, 'Border Patrol Costume', 19.99, 20);
insert into items values (845, 'Meteor Impact Survival Kit', 299.00, 0);
insert into items values (429, 'Air Guitar', 9.99, 14);
commit;

CREATE TABLE orders (
    order_id number,
    cust_id number, 
    order_date timestamp);

insert into orders values (1, 1, SYSDATE);
insert into orders values (2, 1, SYSDATE);
insert into orders values (3, 3, SYSDATE);
insert into orders values (4, 2, SYSDATE);

CREATE TABLE lineitems (
    order_id number, 
    item_id  number,
    quantity number);

insert into lineitems values(1, 845, 1);
insert into lineitems values(2, 232, 1);
insert into lineitems values(2, 429, 4);
insert into lineitems values(3, 122, 1);
insert into lineitems values(4, 345, 1);

Because of the normalization of the data some tables are hard to read; you need to follow the relationships expressed by the id's to find out the name of ordered items for example. 
The want the JSON we generate from this data to be a bit more readable - so we will perform lookups.

1) Creating a JSON representation for every customer using JSON_OBJECT:

SELECT JSON_OBJECT ('id2' VALUE cust_id,
                                           'name' VALUE (first ||' '|| last),
                                           'joined' VALUE join_date) FROM customers;

--------------------------------------------------------------------------------
{"id2":1,"name":"Eric Cartman","joined":"2017-07-10T16:26:15"}
{"id2":2,"name":"Kenny McCormick","joined":"2017-07-10T16:26:15"}
{"id2":3,"name":"Kyle Brofloski","joined":"2017-07-10T16:26:15"}
{"id2":4,"name":"Stan Marsh","joined":"2017-07-10T16:26:15"}

The JSON_OBJECT functions takes a list of key/value pairs (separated by the keyword 'VALUE') and returns a JSON object for each row. The data field used the ISO-8601 data format.

2) A simple JSON array

A similar function is JSON_ARRAY which creates one JSON array for every input row.

SELECT JSON_ARRAY(first, last) FROM customers;
--------------------------------------------------------------------------------
["Eric","Cartman"]
["Kenny","McCormick"]
["Kyle","Brofloski"]
["Stan","Marsh"]

3) Create a JSON Array with surplus items using JSON_ARRAYAGG

To create a JSON Array of variable size form multiple input rows we need an aggregate functions: JSON_ARRAYAGG. Here we create a JSON Array containing multiple JSON objects. Where therefore nest the two operators. The selection of surplus items happens in the WHERE clause of the query by comparing the 'stock_quantity' value with a constant (>10).

SELECT JSON_ARRAYAGG(
          JSON_OBJECT('id' VALUE item_id,
                      'name' VALUE name,
                      'quantity' VALUE stock_quantity)) 
FROM items
WHERE stock_quantity > 10;
--------------------------------------------------------------------------------
[{"id":345,"name":"Border Patrol Costume","quantity":20},{"id":429,"name":"Air Guitar","quantity":14}]

4) JSON form multiple tables

Now, nesting the JSON generation functions allow us to create one JSON instance that reflects the state of all four tables; this means that we need to join the four tables - again, a strength of a hybrid system that provides you native JSON processing without sacrificing relational features likes join. Also, SQL allows us to do calculations, like counting the number of orders and multiplying the price of a single item with its quantity. We use subqueries with join conditions in the WHERE clause to aggregate the information. The number of rows is driven by the 'customers' table, then for each customer we query the other three table to find the orders and the prices and names of the ordered items:

SELECT JSON_OBJECT(
          'id' VALUE c.cust_id, 
          'name' VALUE (c.first || ' ' || c.last),
          'num_orders' VALUE (
               SELECT COUNT(*)
               FROM orders o
               WHERE o.cust_id = c.cust_id),
          'orders' VALUE (
               SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                         'order_id' VALUE o.order_id,  
                         'date' VALUE o.order_date,
                         'items' VALUE (
                              SELECT JSON_ARRAYAGG (
                                   JSON_OBJECT(
                                        'id' VALUE l.item_id,  
                                        'name' VALUE i.name,
                                        'quantity' VALUE l.quantity,
                                        'single_item_price' VALUE i.price,
                                        'total_price' VALUE (i.price * l.quantity)))
                               FROM lineitems l, items i
                               WHERE l.order_id = o.order_id
                               AND i.item_id = l.item_id)))
          FROM orders o
          WHERE o.cust_id = c.cust_id) ABSENT ON NULL)
FROM customers c;
--------------------------------------------------------------------------------
{"id":1,"name":"Eric Cartman","num_orders":2,"orders":[{"order_id":1,"date":"2017-07-10T16:26:15","items":[{"id":845,"name":"Meteor Impact Survival Kit","quantity":1,"single_item_price":299,"total_price":299}]},{"order_id":2,"date":"2017-07-10T16:26:15","items":[{"id":232,"name":"Rubber Christmas Tree","quantity":1,"single_item_price":65,"total_price":65},{"id":429,"name":"Air Guitar","quantity":4,"single_item_price":9.99,"total_price":39.96}]}]}

{"id":2,"name":"Kenny McCormick","num_orders":1,"orders":[{"order_id":4,"date":"2017-07-10T16:26:15","items":[{"id":345,"name":"Border Patrol Costume","quantity":1,"single_item_price":19.99,"total_price":19.99}]}]}

{"id":3,"name":"Kyle Brofloski","num_orders":1,"orders":[{"order_id":3,"date":"2017-07-10T16:26:15","items":[{"id":122,"name":"Potato Gun","quantity":1,"single_item_price":29.99,"total_price":29.99}]}]}

{"id":4,"name":"Stan Marsh","num_orders":0}

(to pretty print these long JSON results use a tool like JSON-Lint)

Null handling

You see an 'ABSENT ON NULL' in a JSON_OBJECT function above. It specifies how a NULL value is handled. By default a NULL value is omitted in JSON_ARRAY but not omitted in JSON_OBJECT. In the example we want to avoid printing an empty array for customers who have not ordered anything. This is why the record for Stan has no  key 'orders'. The other option is 'NULL ON NULL' which would have created a 'null' value for the 'orders' field.

5) Create a Hashmap of users with JSON_OBJECTAGG

Find out more?