15 Loading External JSON Data

You can create a database table of JSON data from a file-system file containing textual JSON documents.

This topic shows how to populate a table with JSON documents from an external, file-system file, PurchaseOrders.json, which you can obtain from GitHub at https://github.com/oracle/db-sample-schemas/tree/master/order_entry.

The file contains JSON objects, one per line. This format is compatible with the export format produced by common NoSQL databases, including Oracle NoSQL Database.

You can query such an external table directly. For better performance, if you have multiple queries that target different rows, you can populate an ordinary database table or a JSON collection table from the data in the external table.

Example 15-1 creates a database directory that can access the file-system folder to which file PurchaseOrders.json was downloaded from GitHub.

Example 15-2 then uses this database directory to create and fill an external table, json_file_contents, with the data from PurchaseOrders.json. It bulk-fills the external table completely, copying all of the JSON documents to column json_document.

Example 15-4 copies the JSON documents from the external table to JSON column po_document of ordinary database table j_purchaseorder.

Example 15-5 is similar, but it populates JSON collection table purchaseorders instead.

Because we chose JSON-type storage for JSON column json_document of the external table, column po_document of the ordinary table must also be of JSON data type. Example 15-3 creates table j_purchaseorder with JSON column po_document.

See Also:

• Oracle Database Concepts for overview information about external tables

• Oracle Database Utilities and Oracle Database Administrator’s Guide for detailed information about external tables

• Oracle Database Data Warehousing Guide

• CREATE TABLE in Oracle Database SQL Language Reference

Example 15-1 Creating a Database Directory Object for Purchase Orders

You must replace folder-containing-json-file here by the folder where you placed the file that you downloaded from GitHub at https://github.com/oracle/db-sample-schemas/tree/master/order_entry. (That folder must be accessible by the database.)

CREATE OR REPLACE DIRECTORY order_entry_dir
  AS 'folder-containing-json-file';

Note:

You need system privilege CREATE ANY DIRECTORY to create a database directory.

Example 15-2 Creating an External Table and Filling It From a File-System File of Textual JSON Data

CREATE TABLE json_file_contents (data JSON)
  ORGANIZATION EXTERNAL
    (TYPE ORACLE_BIGDATA
     ACCESS PARAMETERS (com.oracle.bigdata.fileformat = jsondoc)
     LOCATION (order_entry_dir:'PurchaseOrders.json'))
  PARALLEL
  REJECT LIMIT UNLIMITED;

Example 15-3 Creating a Table With a JSON Column for JSON Data

Table j_purchaseorder has primary key id and JSON column po_document, which is stored using JSON data type.

DROP TABLE j_purchaseorder;

CREATE TABLE j_purchaseorder
  (id          VARCHAR2 (32) NOT NULL PRIMARY KEY,
   date_loaded TIMESTAMP (6) WITH TIME ZONE,
   po_document JSON);

Example 15-4 Copying JSON Data From an External Table To a Database Table

INSERT INTO j_purchaseorder (id, date_loaded, po_document)
  SELECT SYS_GUID(), SYSTIMESTAMP, data
    FROM json_file_contents;

Example 15-5 Copying JSON Data From an External Table To a JSON Collection Table

This example creates JSON collection table purchaseorders and populates it with the data in external table json_file_contents.

CREATE JSON COLLECTION TABLE purchaseorders;

INSERT INTO purchaseorders SELECT * FROM json_file_contents;