A blog about JSON in the Oracle database Technology

  • July 11, 2017

Generating JSON data

Beda Hammerschmidt

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);


    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);


    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:

                                           '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;


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).

          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:

          '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(
                         'order_id' VALUE o.order_id,  
                         'date' VALUE o.order_date,
                         'items' VALUE (
                              SELECT JSON_ARRAYAGG (
                                        '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

Let's assume we want to retrieve n object containing the customers id's as keys and their corresponding name as value. This is again an aggregation since multiple input rows (one per customer) lead to the creation of one output (the JSON object).
SELECT JSON_OBJECTAGG(to_char(cust_id) VALUE (first ||' '|| last)) FROM customers;
{"1":"Eric Cartman","2":"Kenny McCormick","3":"Kyle Brofloski","4":"Stan Marsh"}
JSON_OBJECTAGG allows only exactly one key-value-pair. And the keys should be unique, otherwise the same key is associated with multiple values.


Find out more?

Leave a comment or read more about the JSON generation functions in Oracle here: https://docs.oracle.com/database/122/ADJSN/generation.htm

Join the discussion

Comments ( 8 )
  • Stew Ashton Friday, July 14, 2017
    To pretty print the result of these functions, we can wrap them in a JSON_QUERY call:

    SELECT json_query( JSON_OBJECT(...), '$' pretty )
    FROM customers c;
  • Shahnawaz Friday, October 5, 2018
    This is a very Nice Blog. If You want t format JSON data then use Json Formatter Online tool.
  • Iori Yagami Friday, October 19, 2018
    How will this fair when it comes to large dataset like 50,000 rows?
  • Alex Friday, October 26, 2018
    this all doesn't make sense because the limit is 4000 and a any time your live system will choke on data because it surpasses varchar2 limit. Sucks....
  • Beda Wednesday, February 27, 2019
    Hi Alex,
    JSON generation is NOT limited to 4000 bytes. First varchar2 supports up to 32k and beyond that you can use CLOB or BLOB as return value.
  • Beda Wednesday, February 27, 2019
    50k rows should be easily supported. JSON generation is being used as a means to export/transfer data to other systems in cases much larger than 50k rows. You will need to use a lob return data type to hold the large generated value.
  • Jesse Loy Tuesday, January 14, 2020
    alguien sabe si en versiones posteriores a la versión 12c de oracle la función JSON_ARRAYAGG se soluciono el problema al retornar un clob con un JSON Valido.
  • Bennie WESLEY Thursday, April 16, 2020
    I am an experienced "full stack" dev and we are unfortunately ONLY on v12.1 of OracleDatabase. :{
    I have studied a lot of these reference materials, and it seems that I will have no choice but to write server-side java or node.js to then send to browser to finally parse to JSON using JS/jQuery, etc. I was looking for solutions like your awesome products here and now I find out I have a long way to go. I guess I am glad I like to code! Do you have ANY advice or soltions for org's not on v.12.2? Do I have NO choice but low-level server side/with browser JS/jQ parsing, etc. Thanks!
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.