Using Oracle SQL Functions for JSON in Oracle Database 12c

By Yuli Vasiliev

Oracle Database 12c introduces Oracle SQL functions for JSON as a key feature of the JSON support. With the help of these functions, you can query JSON data, project it relationally, and even index JSON content. This article gives some examples on how to use Oracle SQL functions for JSON, explaining differences between them.

First, let’s look at an example of querying JSON without the use of an Oracle SQL function. While this is possible, you will be limited on using Oracle JSON path expressions. Thus, for example, you won’t be able to access particular elements of a JSON array. In the following example, you simply want to output the PO numbers, along with the Reference field, of all the POs stored in the po_doc JSON column of the json_po table (you can define this table and populate it with data as described in my previous article (Projecting JSON Data Into Relational Format in Oracle Database):

SELECT po.po_doc.PONo, po.po_doc.Reference FROM json_po po;

The above might produce the following output:

PONO REFERENCE
——— —————–
321 JJAMESON-20150823
425 TFREEMAN-20150825

In the above example, you use a simple dot-notation syntax, which seems very appropriate today. Now suppose you want to output the descriptions of all the line items in the PO with PONo 321. Now you cannot use dot-notation syntax, because it does not allow you to access array elements. This is where the JSON_VALUE and JSON_QUERY Oracle SQL functions come to the rescue with their ability to use Oracle JSON path expressions to drill down into an element, including nested arrays:

SELECT json_query(po_doc, ‘$.LineItems[*].Description’ WITH WRAPPER) as Description
FROM json_po
WHERE json_value(po_doc, ‘$.PONo’ RETURNING NUMBER) = 321;

The above query should produce the following output:

DESCRIPTION
——————-
[“Chocolate”,”Cakes”]

It is interesting to note that in the above query, you don’t have to use JSON_VALUE in the WHERE clause and do with the dot-notation syntax. So, you can change the above query to use the dot-notation syntax in the WHERE clause. It should work too, producing the same output:

SELECT json_query(po_doc, ‘$.LineItems[*].Description’ WITH WRAPPER) as Description
FROM json_po po
WHERE po.po_doc.PONo = 321;

Perhaps the most interesting Oracle SQL function for JSON is JSON_TABLE, allowing you to produce JSON content relationally. Here is an example of using it:

SELECT p.*
FROM json_po po,
JSON_TABLE(po.po_doc, ‘$’
COLUMNS (PONo NUMBER(3) PATH ‘$.PONo’,
Reference VARCHAR2(50 CHAR) PATH ‘$.Reference’,
Requestor VARCHAR2(50 CHAR) PATH ‘$.Requestor’,
POUser VARCHAR2(50 CHAR) PATH ‘$.User’,
CostCenter VARCHAR2(50 CHAR) PATH ‘$.CostCenter’,
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’
))) p;

The above should produce the following output:

PONO REFERENCE REQUESTOR POUSER COS ITN DESCRIPT UNI QUA
————————————————————————–
321 JJAMESON-20150823 John Jameson JJAMESON L80 1 Chocolate 16.45 8
321 JJAMESON-20150823 John Jameson JJAMESON L80 2 Cakes 19.85 1
425 TFREEMAN-20150825 Toe Freeman TFREEMAN N40 1 Christmas Cakes 23.75 4
425 TFREEMAN-20150825 Toe Freeman TFREEMAN N40 2 Bagels 9.95 2

When defining column names in the COLUMNS clause of JSON_TABLE, make sure you do not use Oracle reserved words. Thus, in the above example, I had to replace User with POUser, since the first one is a reserved word.

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.