{ Abhilash Kumar Bhattaram : Follow on LinkedIn }
Oracle Database and JSON
JSON is important because it offers a lightweight, human-readable format that is easy for both developers and machines to work with. Its language- and platform-independent nature makes it ideal for data interchange across diverse systems, especially in modern web services and APIs. JSON’s flexible structure supports complex, nested data, making it well-suited for semi-structured use cases like IoT, logging, and analytics. As a result, it has become the standard format for communication between front-end and back-end systems, enabling faster development and more dynamic application designs.
Now what does Oracle offer for JSON handling of data ?
- Native JSON Storage: Oracle 19c supports storing JSON in VARCHAR2, CLOB, and BLOB with validation using IS JSON constraints. Oracle 21c adds a dedicated JSON datatype with binary-optimized storage.
- Powerful Query Functions: Offers SQL/JSON functions like JSON_VALUE, JSON_QUERY, and JSON_EXISTS for efficient querying.
- Relational Mapping: JSON_TABLE lets you map JSON data to relational columns for easy integration with traditional SQL.
- Indexing & Performance: Supports functional and automatic indexing on JSON paths for optimized query performance.
- APIs & Tools: Includes RESTful access via ORDS and document-style APIs through SODA for flexible JSON operations.
Sample JSON Data Conversion
Using Oracle example schemas as available here here , we are making use of SH.COSTS table to export all the table contents into a JSON file
Checking Tables before JSON Conversion
Let us check the SH.COSTS Table , I see there are 82112 rows which I plan to make them into a JSON format
cdbl> select count(*) from SH.COSTS;
COUNT(*)
--------------------
82112
Elapsed: 00:00:00.00
-- Displaying first 10 rows
cdbl> select * from SH.COSTS where rownum < 10;
PROD_ID|TIME_ID | PROMO_ID| CHANNEL_ID| UNIT_COST| UNIT_PRICE
--------------------|-----------------|--------------------|--------------------|--------------------|--------------------
14|03-MAR-1998 00:00| 999| 4| 863.64| 1176.23
15|02-JAN-1998 00:00| 999| 4| 846.71| 1013.99
15|10-JAN-1998 00:00| 999| 3| 875.22| 999.99
15|22-JAN-1998 00:00| 999| 3| 870.04| 999.99
15|05-FEB-1998 00:00| 999| 4| 887.35| 1003.99
15|16-FEB-1998 00:00| 999| 3| 908.8| 1003.49
18|18-JAN-1998 00:00| 999| 3| 1133.3| 1632.79
18|23-FEB-1998 00:00| 999| 3| 1155.97| 1655.65
18|10-MAR-1998 00:00| 999| 2| 1170.86| 1697.83
9 rows selected.
cdbl> @part
Enter value for table_name: COSTS
Enter value for owner: SH
TABLE_NAME |TABLE_OWNER |PARTITION_NAME |TABLESPACE_NAME | SUBPARTITION_COUNT| NUM_ROWS
----------------------------------------|----------------------------------------|----------------------------------------|------------------|--------------------|--------------------
COSTS |SH |COSTS_Q3_1998 |USERS | 0| 4129
COSTS |SH |COSTS_Q4_1998 |USERS | 0| 4577
COSTS |SH |COSTS_Q1_1999 |USERS | 0| 5884
COSTS |SH |COSTS_Q4_1999 |USERS | 0| 5060
COSTS |SH |COSTS_Q1_2000 |USERS | 0| 3772
COSTS |SH |COSTS_Q2_2000 |USERS | 0| 3715
COSTS |SH |COSTS_Q3_2000 |USERS | 0| 4798
COSTS |SH |COSTS_Q4_2000 |USERS | 0| 5088
COSTS |SH |COSTS_Q1_2001 |USERS | 0| 7328
COSTS |SH |COSTS_Q2_2001 |USERS | 0| 5882
COSTS |SH |COSTS_Q4_2001 |USERS | 0| 9011
COSTS |SH |COSTS_Q3_2001 |USERS | 0| 7545
COSTS |SH |COSTS_Q1_1998 |USERS | 0| 4411
COSTS |SH |COSTS_Q2_1998 |USERS | 0| 2397
COSTS |SH |COSTS_Q2_1999 |USERS | 0| 4179
COSTS |SH |COSTS_Q3_1999 |USERS | 0| 4336
COSTS |SH |COSTS_1995 |USERS | 0| 0
COSTS |SH |COSTS_1996 |USERS | 0| 0
COSTS |SH |COSTS_H1_1997 |USERS | 0| 0
COSTS |SH |COSTS_H2_1997 |USERS | 0| 0
COSTS |SH |COSTS_Q1_2002 |USERS | 0| 0
COSTS |SH |COSTS_Q1_2003 |USERS | 0| 0
COSTS |SH |COSTS_Q2_2002 |USERS | 0| 0
COSTS |SH |COSTS_Q2_2003 |USERS | 0| 0
COSTS |SH |COSTS_Q3_2002 |USERS | 0| 0
COSTS |SH |COSTS_Q3_2003 |USERS | 0| 0
COSTS |SH |COSTS_Q4_2002 |USERS | 0| 0
COSTS |SH |COSTS_Q4_2003 |USERS | 0| 0
28 rows selected.
Elapsed: 00:00:00.02
cdbl>Using 19c JSON_OBJECT Functions
Below is the SQL file I am using to use the 19c JSON_OBJECT function which will help me spool the SQL output into a JSON file
There is large collection of JSON Functiosn which is avaialble is the JSON Developers guide here
NOTE : You would need to use a "set head off" and "set feedback off" as SQL output needs to be a clean JSON text , we will see in later section on how to verify this.
cdbl> ! cat gen_json.sql
set head off
set feedback off
set timing off
spool /home/oracle/costs.json
SELECT JSON_OBJECT(
'PROD_ID' VALUE PROD_ID,
'TIME_ID' VALUE TIME_ID,
'PROMO_ID' VALUE CHANNEL_ID,
'UNIT_COST' VALUE UNIT_COST,
'UNIT_PRICE' VALUE UNIT_PRICE
) AS employee_json
FROM SH.COSTS;
spool offGenerating JSON data from Table
Now I would just run the gen_json.sql as above
cdbl> @gen_json.sql
.
.
.
"PROD_ID":129,"TIME_ID":"2001-10-17T00:00:00","PROMO_ID":3,"UNIT_COST":152.73,"UNIT_PRICE":189.58}
{"PROD_ID":130,"TIME_ID":"2001-10-14T00:00:00","PROMO_ID":2,"UNIT_COST":76.37,"UNIT_PRICE":98.89}
{"PROD_ID":135,"TIME_ID":"2001-12-02T00:00:00","PROMO_ID":4,"UNIT_COST":43.85,"UNIT_PRICE":51.85}
{"PROD_ID":140,"TIME_ID":"2001-10-02T00:00:00","PROMO_ID":4,"UNIT_COST":27.18,"UNIT_PRICE":32.08}
{"PROD_ID":144,"TIME_ID":"2001-12-10T00:00:00","PROMO_ID":4,"UNIT_COST":6.9,"UNIT_PRICE":7.63}
{"PROD_ID":147,"TIME_ID":"2001-10-18T00:00:00","PROMO_ID":3,"UNIT_COST":6.36,"UNIT_PRICE":8.07}
{"PROD_ID":148,"TIME_ID":"2001-12-24T00:00:00","PROMO_ID":3,"UNIT_COST":17.92,"UNIT_PRICE":23.14}
cdbl>You can see above that the row contents are in JSON format spooled top /home/oracle/cust.json
Now to verify JSON with Unix commands
Oracle database has done it's part now it's upto other applications to understand this JSON format.
-- Including empty lines
[oracle@machine1 ~]$ cat /home/oracle/costs.json | wc -l
82525
-- Escluding empty lines
[oracle@machine1 ~]$ cat /home/oracle/costs.json | grep -v ^$ | wc -l
82112. ----->>> Now this matches my table count.
-- Making a clean JSON file
cat /home/oracle/costs.json | grep -v ^$ > /home/oracle/costs_formatted.jsonThere is a utility called jq which is available to verify the JSON contents
[oracle@machine1 ~]$ jq empty /home/oracle/costs.json
[oracle@machine1 ~]$
NOTE : The empty output means your JSON is validNow moving on to filtering JSON data
[oracle@machine1 ~]$ jq 'select(.PROD_ID == 15)' /home/oracle/costs_formatted.json > /home/oracle/costs_formatted_PRODID_15.json
[oracle@machine1 ~]$
-- An example of the filtered data
[oracle@machine1 ~]$ tail -50 /home/oracle/costs_formatted_PRODID_15.json
}
{
"PROD_ID": 15,
"TIME_ID": "2001-11-16T00:00:00",
"PROMO_ID": 2,
"UNIT_COST": 786.6,
"UNIT_PRICE": 926.79
}
{
"PROD_ID": 15,
"TIME_ID": "2001-12-02T00:00:00",
"PROMO_ID": 4,
"UNIT_COST": 746.2,
"UNIT_PRICE": 877.9
}
{
"PROD_ID": 15,
"TIME_ID": "2001-12-16T00:00:00",
"PROMO_ID": 3,
"UNIT_COST": 801.1,
"UNIT_PRICE": 914.38
}
{
"PROD_ID": 15,
"TIME_ID": "2001-12-16T00:00:00",
"PROMO_ID": 4,
"UNIT_COST": 746.2,
"UNIT_PRICE": 877.9
}
{
"PROD_ID": 15,
"TIME_ID": "2001-12-26T00:00:00",
"PROMO_ID": 3,
"UNIT_COST": 801.1,
"UNIT_PRICE": 914.38
}
{
"PROD_ID": 15,
"TIME_ID": "2001-10-16T00:00:00",
"PROMO_ID": 2,
"UNIT_COST": 791.91,
"UNIT_PRICE": 929.71
}
{
"PROD_ID": 15,
"TIME_ID": "2001-10-03T00:00:00",
"PROMO_ID": 4,
"UNIT_COST": 754.86,
"UNIT_PRICE": 889
}
[oracle@machine1 ~]$Verifying filtered data with SQL and with jq
Here is the SQL version of JSON record count
dbl> select count(*) from SH.COSTS where PROD_ID=15;
COUNT(*)
--------------------
874
cdbl>Now checking the same with jq
[oracle@machine1 ]$ jq 'select(.PROD_ID == 15)' /home/oracle/costs_formatted.json | grep PROD_ID | wc -l
874. <<<---- 874 matches !!!You can see now that the count 874 matches with SQL and with jq , there are a million such possibilities that each applications offers to convert raw SQL data to JSON format.
Hope you learnt something from this blog.