Oracle Capabilities — Handling of Json
Let we examine various Json sql functions to manipulate Json Data
With the help of versatile use cases, we will see the oracle capabilities to wangle json data
List of use cases:
Case:1.0 Let we create few tables to load for json data.
We can store JSON data in Oracle Database using data types as VARCHAR2, LOB like (CLOB, or BLOB) and JSON ( native binary format).
JSON SQL conditions “is json” complementary check which helps whether json data is syntactically correct while loading data into table.
These are sample sql statement to create a table to store json document.
CREATE TABLE j_purchaseorder
(id VARCHAR2 (32) NOT NULL PRIMARY KEY,
po_document VARCHAR2 (32767)
CONSTRAINT ensure_json CHECK (po_document IS JSON));
CREATE TABLE PURCHASE_ORDER
(ID RAW(16) NOT NULL,
DATE_LOADED TIMESTAMP(6) WITH TIME ZONE,
PO_DOCUMENT [ CLOB | BLOB ] CHECK (PO_DOCUMENT IS JSON)) ;
CREATE TABLE k_purchaseorder
(id VARCHAR2 (32) NOT NULL PRIMARY KEY,
date_loaded TIMESTAMP (6) WITH TIME ZONE,
po_document JSON); => # from 21c to store in Binary type (native json)
Case:2.0/ Loading Json data As external Table
Json data can be loaded in different methods. We can load json dump as external table, using type oracle loader,
As we see here, first we need create oracle directory and create external table referring to that oracle directory which has json data and with keyword TYPE ORACLE_LOADER.
create or replace directory ORDER_ENTRY as ‘/u01/workshop/dump’;
CREATE TABLE PURCHASE_EXT(JSON_DOCUMENT CLOB)
ORGANIZATION EXTERNAL( TYPE ORACLE_LOADER
DEFAULT DIRECTORY ORDER_ENTRY
ACCESS PARAMETERS (
RECORDS DELIMITED BY 0x’0A’
DISABLE_DIRECTORY_LINK_CHECK
BADFILE ORDER_ENTRY: ‘PURCHASE_EXT.bad’
LOGFILE ORDER_ENTRY: ‘PURCHASE_EXT.log’
FIELDS(JSON_DOCUMENT CHAR(5000)) )
LOCATION ( ORDER_ENTRY:’PurchaseOrders.dmp’))
PARALLEL
REJECT LIMIT UNLIMITED;
Case:3.1/ Select Json Values from External table
select count(*) from purchase_ext;
select * from purchase_ext where rownum <= 2;
case:3.2/ Query json values(few cols) from external table
select j.JSON_DOCUMENT.PONumber,
j.JSON_DOCUMENT.Reference,
j.JSON_DOCUMENT.Requestor,
j.JSON_DOCUMENT.CostCenter,
j.JSON_DOCUMENT.ShippingInstructions.Address.city as City,
j.JSON_DOCUMENT.LineItems.ItemNumber as ItemNumber,
j.JSON_DOCUMENT.LineItems.Part as Part,
j.JSON_DOCUMENT.LineItems.Part.Description as PDesc
from purchase_ext j where
j.JSON_DOCUMENT.PONumber =’100';
case: 4/ Creation of Partition Table
To create partition for json data, we should create virtual column.
Using virtual column, we can create partition on json data .
Here, po_num_vc is virtual column and range partition is built on virtual column.
CREATE TABLE j_purchaseorder_partitioned
(id VARCHAR2 (32) NOT NULL PRIMARY KEY,
date_loaded TIMESTAMP (6) WITH TIME ZONE,
po_document CLOB,
po_num_vc NUMBER GENERATED ALWAYS AS
(json_value (po_document, ‘$.PONumber’ RETURNING NUMBER)))
LOB (po_document) STORE AS (CACHE)
PARTITION BY RANGE (po_num_vc)
( PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000));
case:5.1/ Insert a record in (normal) json table
We can load or Insert a JSON document into a JSON column table using insert Sql statement which is a straightforward. Here is sample insert sql query which inserts json document into relational table
INSERT INTO PURCHASE_ORDER
VALUES (
SYS_GUID(),
to_date(‘05-MAY-2021’),
‘{“PONumber” : 10011,
“Reference” : “SBELL-20141017”,
“Requestor” : “Sarah Bell”,
“User” : “SBELL”,
“CostCenter” : “A50”,
“ShippingInstructions” :
{“name” : “Sarah Bell”,
“Address” : {“street” : “200 Sporting Green”,
“city” : “South San Francisco”,
“state” : “CA”,
“zipCode” : 99236,
“country” : “United States of America”},
“Phone” : “983–555–6509”},
“SpecialInstructions” : “Courier”,
“LineItems” :
[ {“ItemNumber” : 1,
“Part” :
{“Description” : “Making the Grade”,
“UnitPrice” : 20,
“UPCCode” : 27616867759
}, “Quantity” : 8.0 },
{“ItemNumber” : 2,
“Part” :
{“Description” : “Nixon — Eric”,
“UnitPrice” : 19.95,
“UPCCode” : 717951002396
}, “Quantity” : 5 },
{“ItemNumber” : 3,
“Part” : {“Description” : “Eric Clapton: Best Of 1981–1999”,
“UnitPrice” : 19.95,
“UPCCode” : 75993851120
}, “Quantity” : 5.0 } ] }’ );
case:5.2/ Insert a another record which has additional Key Value(phone) in (normal) json table
case:6/ Query JSON data: Using Dot Notation
Using dot notation, we can query the json key, value and also the elements in its array. Here itemno , is array of Key value pairs , which we can query using dot notation
case:7/ Query JSON data: Using JSON Functions (JSON_VALUE)
Another method to query json document can be done through Json query or Json value sql-functions.
Jsonvalue , selects a scalar value from JSON data, as a SQL value.
case:8.1/ Query JSON data: Using JSON Functions (JSON_QUERY)
Using JSON_QUERY , we can select element from json array based on index value as sql string
case:8.2/ Query JSON data: Using JSON Functions (JSON_QUERY) with additional example
case:9/ Filtering JSON data: Using JSON Functions (JSON_EXISTS)
To filter the json document, we can use json_exists ,which lets us to use a SQL/JSON path expression as a row filter, to select rows based on the content of JSON documents. Here we try to filter json document based on UPCode to a given value and itemnumber = 2 using json_exists
case:10.1/ Query JSON data: Using JSON Functions (JSON_TABLE)
Another method of query json document can be done by using json_table sql function which projects specific JSON data to columns of various SQL data types like numbers, varchars nchar and it is used to map parts of a JSON document into the rows and columns of a new, virtual table like inline-view,
Using this virtual table, we can insert only those columns based on our selection into another pre-existing table or create new relational table with associated datatypes.
case:10.2/ Creating Relational table from JSON data: Using JSON Functions (JSON_TABLE)
Now, we can describe
desc R_PURCHASE_ORDER;
case:11.1/ Store JSON data as BLOB
alter table PURCHASE_ORDER add (po_document_blob blob) add CONSTRAINT json_check_blob CHECK (po_document_blob IS JSON);
update PURCHASE_ORDER set po_document_blob=po_document;
case:11.2/ Query JSON CLOB and BLOB data
— reading json’s CLOB data
select po_document from PURCHASE_ORDER where rownum <=2;
— reading json’s blob data
select po_document_blob from PURCHASE_ORDER where rownum <=2;
case:11.3/ Query JSON BLOB data: Using JSON_SERIALIZE Functions (without PRETTY)
select json_serialize(j.po_document_blob) as po_document_blob from PURCHASE_ORDER j where rownum <=1;
case: 11.4/ Reading Json Blob data in readable format using json_serialize (with PRETTY)
select json_serialize(j.po_document_blob PRETTY) as po_document_blob from PURCHASE_ORDER j where rownum <=1;
case:12.0/ Filter JSON BLOB data: Using JSON_EXISTS Functions
case:13.1/ Update JSON data: Using JSON_MERGEPATCH function
Updating of existing Json data can be done using json_mergepatch sqlfunction , where this function is used to update or merge specific portions of a JSON document with other json object. It is something like, patching the existing json document with some json objects
case:13.2/ Update JSON data: Using JSON_MERGEPATCH (Modifying Entire Array)
case: 14: Update Json Data: using JSON_TRANSFORM function ( from 21c)
Json’s SQL function json_transform modifies JSON documents to update a specific field record inside an array.
i.e we can add, remove, update particular array of element. In this example, we are updating the value of column of job and salary, and we are removing the phone number for type = work and appending value for key children
In earlier release we use SQL function json_mergepatch, which has limited capability for JSON update has been enhanced with this new function json_transform
Example:
I invite you to watch complete demo video here for better clarity.
For any question please reach out to me at mail2crsathishkumar@gmail.com
In the next article i shall deliver upon Migration method -2 (Migrating MongoDB collections to Oracle Autonomous Database using SODA api)
