Oracle

Loading JSON Through External Tables

by Yuli Vasiliev

Loading JSON Through External Tables

By Yuli Vasiliev

The article illustrates the simplest way to load external JSON data to an Oracle database through external tables. The example provided uses the dump file containing JSON documents. This file comes with Oracle Database 12c as a sample. All you’ll need to do to follow the sample code provided in this article is to adjust the path to this dump file according to your system.

Let’s start with creating a new user in your Oracle database to play with. After that, you’ll need to grant the connect and resource roles to that test user so that you can use it for the article example. This can be done with the following code you can issue from within SQL*Plus or similar tool when connected as SYSDBA:

CREATE USER json_test IDENTIFIED BY pswd;

GRANT connect, resource TO json_test;

Also, to avoid the no privileges on tablespace error, you can grant the unlimited tablespace to the user, as follows. (alternatively, you could grand a particular number of MB):

GRANT UNLIMITED TABLESPACE TO json_test;

Next, create a directory for the one that contains sample dump file PurchaseOrders.dmp, and grant the privileges on it to the test user. Note that your actual path may differ from the one used in the code below:

CREATE OR REPLACE DIRECTORY order_dir

AS ‘/home/oracle/app/oracle/product/12.1.0/dbhome_1/demo/schema/order_entry’;

GRANT READ, WRITE ON DIRECTORY order_dir TO json_test;

Now you can connect as the test user and create the external table for loading JSON content from the dump file. Below is the simplest version of the CREATE TABLE command you can use for that:

CONN json_test/pswd

CREATE TABLE ext_json (json_doc CLOB)

ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY order_dir

ACCESS PARAMETERS

(RECORDS DELIMITED BY NEWLINE

FIELDS (json_doc CHAR(5000)))

LOCATION (order_dir:’PurchaseOrders.dmp’))

PARALLEL

REJECT LIMIT UNLIMITED;

Once the external table has been created you can query it as a regular table. For example, you might want to count the number of rows in this table (each of which represents a JSON document loaded from the PurchaseOrders.dmp dump file):

SELECT COUNT(*) FROM ext_json;

COUNT(*)

———

10000

Of course, you can issue a more sophisticated query, addressing particular JSON pairs in the queried documents. Thus, in the following example, you retrieve the references of the orders, which have Martha Sullivan as the requestor and have at least 7 line items.

SELECT a.json_doc.Reference

FROM ext_json a

WHERE a.json_doc.Requestor = ‘Martha Sullivan’ and JSON_EXISTS (json_doc, ‘$.LineItems[6]’);

REFERENCE

—————–

MSULLIVA-20141025

MSULLIVA-20140417

MSULLIVA-20141211

MSULLIVA-20140720

MSULLIVA-20141014

You may not only query the external table directly, which mediates between the dump file containing external JSON content and the database, but also create a regular table in the database to load that content in:

CREATE TABLE json_docs (

id RAW(16) NOT NULL,

po_doc CLOB,

CONSTRAINT json_docs_pk PRIMARY KEY (id),

CONSTRAINT json_docs_json_chk CHECK (po_doc IS JSON)

);

After the table has been created, you can load it with the data from the dump file. You can load all the documents at once, or partly, using, for example, JSON SQL function JSON_EXISTS in the WHERE clause to filter the incoming content, as follows:

INSERT INTO json_docs

SELECT SYS_GUID(), json_doc FROM ext_json

WHERE (json_doc IS JSON) AND (JSON_EXISTS (json_doc, ‘$.LineItems[6]’));

406 rows inserted.

As you might guess, the loading process in the above example affected only those PO documents that include al least 7 line items.

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.