Using JSON_TABLE To Shred JSON Into Relational Data
By Yuli Vasiliev
Being less verbose than XML, JSON is becoming more and more popular as a data exchange format in Web services and Web applications today. Starting with release 12.1, Oracle Database supports JSON, letting you access JSON content stored either inside or outside of the database. The example provided in this article illustrates how you might project JSON data relationally, consolidating it with regular relational data or data obtained in another format and then converted into relational format on the fly.
It is interesting to note that JSON support in Oracle Database is implemented through Oracle XML DB – a set of Oracle Database XML technologies integrated with the relational database server. And starting with Oracle Database 12c Release 1, Oracle XML DB is a mandatory component of the database. So, not surprisingly, SQL access to XML and SQL access to JSON work in a like manner in Oracle Database. Both are based on Oracle SQL functions. In case of XML, they are referred to as SQL XML functions. In case of JSON – SQL JSON functions.
When it comes to shredding JSON data into relational data you can use SQL JSON function JSON_TABLE, which is similar to XMLTable used for shredding XML data into relational data. In the following example, you use JSON_TABLE to shred JSON obtained from an external source, making a SQL call to the Google Web Search API that allows you to do programmatic web searches over HTTP.
SELECT * FROM JSON_TABLE(
httpuritype(‘http://ajax.googleapis.com/ajax/services/search/web?v=1.0&q=SSWUG%20Yuli%20Vasiliev’).getCLOB(), ‘$.responseData.results[*]’
COLUMNS (title VARCHAR2(100 CHAR) PATH ‘$.titleNoFormatting’,
url VARCHAR2(100 CHAR) PATH ‘$.url’));
Looking at the JSON_TABLE construction in the above query, you may notice the use of the COLUMNS clause, which has the same functionality as this clause has in XMLTable — defines the columns of the virtual table to be created.
The output of the above query may look like this:
TITLE
———————————————————————
URL
———————————————————————
Using Oracle SQL Functions for JSON in Oracle Database 12c – SSWUG.ORG
http://bitonthewire.wpengine.com/yulivasiliev/oracle/using-oracle-sql-functions-for-json-in-oracle-database-12c/
Using SQL/XML functions XMLExists and XMLCast – SSWUG.ORG
http://bitonthewire.wpengine.com/yulivasiliev/oracle/using-sqlxml-functions-xmlexists-and-xmlcast/
…
As you can see, the output is presented in the form of relational rows and, therefore, can be easily joined with another relational data. Suppose you have database table articles that contains information about published articles, so that you can easily build the following join:
SELECT art.pubDate pubdate, jt.title title, jt.url url FROM JSON_TABLE(
httpuritype(‘http://ajax.googleapis.com/ajax/services/search/web?v=1.0&q=SSWUG%20Yuli%20Vasiliev’).getCLOB(), ‘$.responseData.results[*]’
COLUMNS (title VARCHAR2(100 CHAR) PATH ‘$.titleNoFormatting’,
url VARCHAR2(100 CHAR) PATH ‘$.url’)) jt,
articles art
WHERE art.title = jt.title;
The join output might look like this:
PUBDATE
———————————————————————
TITLE
———————————————————————
URL
———————————————————————
September 3, 2015
Using Oracle SQL Functions for JSON in Oracle Database 12c – SSWUG.ORG
http://bitonthewire.wpengine.com/yulivasiliev/oracle/using-oracle-sql-functions-for-json-in-oracle-database-12c/
May 19, 2014
Using SQL/XML functions XMLExists and XMLCast – SSWUG.ORG
http://bitonthewire.wpengine.com/yulivasiliev/oracle/using-sqlxml-functions-xmlexists-and-xmlcast/
…
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.