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.
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.
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"]
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}]
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)
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.
SELECT json_query( JSON_OBJECT(...), '$' pretty )
FROM customers c;
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.
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.
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!
Ben