{ 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 off Generating 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.json There 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 valid Now 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.
Top comments (0)