JSON in Oracle database

Lets first start with what is JSON

What is JSON

JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. It is based on a subset of the JavaScript Programming Language, Standard ECMA-262 3rd Edition – December 1999. JSON is a text format that is completely language independent but uses conventions that are familiar to programmers of the C-family of languages, including C, C++, C#, Java, JavaScript, Perl, Python, and many others. These properties make JSON an ideal data-interchange language.

How to use JSON in Oracle Database

JSON is supported in Oracle from 12c. Now we can store JSON data in the clob column and insert,query,extract as required easily

Simple Steps

(1) create a JSON column with an is json check constraint
(2) insert JSON data into the column
(3) query the JSON data.

Lets understand with an example

(1)First lets create the table to store the JSON Data

Below statement create table with JSON column exp_json_value

create table exp_json
(
id number,
exp_json_value clob constraint exp_json_chk check (exp_json_value is json (with unique keys))
);

So “is json” is the key to create the JSON column

(2) Now lets insert the JSON data in the table

(3) Now data is created , we can extract using queries

SELECT d.exp_json_value.PONumber FROM exp_json d;
PONumber
-------
1600

Other Similar queries will be

SELECT d.exp_json_value.ShippingInstructions.Phone FROM exp_json d;
SELECT d.exp_json_value.LineItems FROM exp_json d;
select json_value(exp_json_value,'$.ShippingInstructions[0].name') from exp_json;
SELECT d.exp_json_value.ShippingInstructions.Phone.type FROM exp_json d;

few rules that you need to bear in mind

• Your table must have an alias; you cannot choose to skip this step and simply use the table name. It must be aliased.
• Your column must be a JSON column, by which I mean that it must be signposted as containing JSON data by the existence of an IS JSON check constraint. As always, while this constraint must exist, it does not need to be enabled.
• The JSON object key in your query must be a case-sensitive match for the object key whose value you are seeking to retrieve.

Others was to extract the data from JSON object

There are other ways to extract the data from JSON objects.Lets see them below

• $ – The context item.
• $.friends – The value of key friends of the context-item object. The dotted notation indicates that the context item is a JSON object.
• $.friends[0] – The object that is the first element of the array that is the value of key friends of the context-item object. The bracket notation indicates that the value of key friends is an array.
• $.friends[0].name – Value of key name of the object that is the first element of the array that is the value of key friends of the context-item object. The second dot indicates that the first element of array friends is an object (with a name key).
• $.friends[*].name – Value of key name of each object in the array that is the value of key friends of the context-item object.
• $.*[*].name – Key name values for each object in an array value of a key of the context-item object.
• $.friends[3, 8 to 10, 12] – The third, eighth, ninth, tenth, and twelfth elements of array friends (key of the context-item object). The elements must be specified in ascending order, and they are returned in that order: third, eighth, ninth, tenth, twelfth.
• $friends[3].cars – The value of key cars of the object that is the third element of array friends. The dot indicates that the third element is an object (with a cars key).
• $friends[3].* – The values of all of the keys of the object that is the third element of array friends.
• $friends[3].cars[0].year – The value of key year of the object that is the first element of the array that is the value of key cars of the object that is the third element of the array that is bound to variable friends.

Example based on these

SQL> select json_value(exp_json_value,'$.ShippingInstructions[1].Address.city') from exp_json;
JSON_VALUE(EXP_JSON_VALUE,'$.SHIPPINGINSTRUCTIONS[1].ADDRESS.CITY')
------------------------
South San Francisco

SQL> select json_value(exp_json_value,'$.Requestor') from exp_json;
JSON_VALUE(EXP_JSON_VALUE,'$.REQUESTOR')
----------------------
Alexis Bull

SQL> select json_value(exp_json_value,'$."ShippingInstructions"') from exp_json;
JSON_VALUE(EXP_JSON_VALUE,'$."SHIPPINGINSTRUCTIONS"')
------------------------------------

SQL> select json_value(exp_json_value,'$.CostCenter' ) from exp_json;
JSON_VALUE(EXP_JSON_VALUE,'$.COSTCENTER')
---------------
A50

So similarly we can create more rows in the table and fire queries to extract the data

SQL> insert into exp_json
values
('30',
'{"PONumber" : 1600, 2
"Reference" : "ABULL-20140421", 3
"Requestor" : "Alexis Bull", 4
"User" : "ABULL" 5
}'); 
1 row created.
SQL> commit;
Commit complete.



SQL>
insert into exp_json
values
('40',SQL>
'{"PONumber" :[ 1600,11],

"Reference" : "ABULL-20140421", 
"Requestor" : "Alexis Bull", 
"User" : "ABULL" 
}');

1 row created.
SQL> commit;
Commit complete.

SQL> select json_value(exp_json_value,'$.PONumber' ) from exp_json;
JSON_VALUE(EXP_JSON_VALUE,'$.PONUMBER')
------------------------------------
1600
1600
1600

SQL> select json_value(exp_json_value,'$.PONumber[1]' ) from exp_json;
JSON_VALUE(EXP_JSON_VALUE,'$.PONUMBER[1]')
-------------------------------------
11
SQL>

Hope you like this post on JSON documents in oracle. Please do provide the Feedback

Also Reads
Primary key in Oracle : primary key in oracle uniquely identify the row in the table. It cannot be null & can be created at the time of table creation or after the table is created
create table oracle as select :Tables are the basic unit of data storage in an Oracle Database.we covers how to use Oracle create table command to create table with foreign key /primary key,create table as select
how to check trigger status in oracle: Check out How to check Trigger status in Oracle, how to enable/disable trigger, how to find the trigger definition, how to find all the triggers
Query to find object dependencies in oracle :Check out for Query to find object dependencies in oracle, child level dependency, parent level dependency, finding using dbms_utility.get_dependency
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/json-in-oracle-database.html#GUID-A8A58B49-13A5-4F42-8EA0-508951DAE0BB