Projecting JSON Data Into Relational Format in Oracle Database

By Yuli Vasiliev

It is common for Web services and Web applications today to use JSON as a data-interchange format. Starting with release 12.1, Oracle Database supports JSON, allowing you to access JSON content stored either inside or outside of the database. This article explains how you might project JSON data relationally, so that you can consolidate it with the data obtained from another data source.

Often, you may want to project JSON data relationally when you need to join it to the data from another source. As the tool to convert JSON content into relational data, you use the JSON_TABLE Oracle SQL function, which shreds the result of a JSON expression evaluation into relational rows, producing a virtual relational table that can be then joined with one or more other tables.

It is interesting to note that JSON support in Oracle Database 12c Release 1 (12.1.0.2) does not provide a data type designed specially to hold JSON content. You can use VARCHAR2, CLOB, or BLOB for that, applying an IS JSON check constraint to make sure that the column contains well-formed JSON data. That’s the only condition checked by IS JSON – no check for a schema that defines the data is performed at this stage. Below is a simple example of how you might create a table with a JSON column:

DROP TABLE json_po PURGE;

CREATE TABLE json_po (
id RAW(16) NOT NULL,
po_doc CLOB,
CONSTRAINT json_chk CHECK (po_doc IS JSON)
);

Now you can insert a few rows into the above table to play with. The inserted below JSON represents a simplified purchase order (PO) document:

INSERT INTO json_po
VALUES (SYS_GUID(),
‘{“PONo” : 321,
“Reference” : “JJAMESON-20150823”,
“Requestor” : “John Jameson”,
“User” : “JJAMESON”,
“CostCenter” : “L80”,
“LineItems” : [
{“ItemNumber” : 1,
“Description” : “Chocolate”,
“UnitPrice” : 16.45,
“Quantity” : 8.0},
{“ItemNumber” : 2,
“Description” : “Cakes”,
“UnitPrice” : 19.85,
“Quantity” : 1.0}
]}’);

INSERT INTO json_po
VALUES (SYS_GUID(),
‘{“PONo” : 425,
“Reference” : “TFREEMAN-20150825”,
“Requestor” : “Toe Freeman”,
“User” : “TFREEMAN”,
“CostCenter” : “N40”,
“LineItems” : [
{“ItemNumber” : 1,
“Description” : “Christmas Cakes”,
“UnitPrice” : 23.75,
“Quantity” : 4.0},
{“ItemNumber” : 2,
“Description” : “Bagels”,
“UnitPrice” : 9.95,
“Quantity” : 2.0}
]}’);

Now that you have some JSON data in the json_po table, you can issue queries against it. Let’s start with an example of simple dot-notation access to JSON. Suppose you want to see requestors for each PO stored in the json_po table. Using the dot notation syntax, this can be done as follows:

SELECT po.po_doc.Requestor FROM json_po po;

REQUESTOR
———-
John Jameson
Toe Freeman

Using dot-notation syntax, though, has some serious limitations. For example, it does not allow you to access particular elements of an array. To overcome these limitations you can use the Oracle SQL functions for JSON, such as JSON_VALUE and JSON_TABLE. The latter can be especially useful when you need to project JSON data relationally. Here is an example of how JSON_TABLE might be used to produce a relational projection of the JSON data stored in the json_po table:

SELECT l.*, d.*
FROM json_po po,
JSON_TABLE(po.po_doc, ‘$’
COLUMNS (PONo NUMBER(3) PATH ‘$.PONo’,
NESTED PATH ‘$.LineItems[*]’
COLUMNS (
ItemNumber NUMBER(3) PATH ‘$.ItemNumber’,
Description VARCHAR2(50 CHAR) PATH ‘$.Description’,
UnitPrice NUMBER(8,2) PATH ‘$.UnitPrice’,
Quantity NUMBER(10,2) PATH ‘$.Quantity’
))) l,
JSON_TABLE(po.po_doc, ‘$’
COLUMNS (PONo NUMBER(3) PATH ‘$.PONo’,
Reference VARCHAR2(50 CHAR) PATH ‘$.Reference’,
Requestor VARCHAR2(50 CHAR) PATH ‘$.Requestor,
User VARCHAR2(50 CHAR) PATH ‘$.User,
CostCenter VARCHAR2(50 CHAR) PATH ‘$.CostCenter,
)) d
WHERE l.PONo=d.PONo;

Alternatively, you could define two views on the PO document and its LineItems array, respectively.

About the Author

Yuli Vasiliev is a software developer, freelance author, and consultant currently specializing in open source development, Java technologies, business intelligence (BI), databases, and service-oriented architecture (SOA). He is the author of Oracle Business Intelligence: The Condensed Guide to Analysis and Reporting (Packt, 2010) and PHP Oracle Web Development (Packt, 2007) as well as a series of other books on the Oracle technology.