JSON_VALUE, JSON_Query & JSON_table function to handle JSON data in Oracle

Leave a reply

JSON_VALUE, JSON_QUERY & JSON_TABLE function use with example in Oracle Database 

What is JSON?

JSON (JavaScript Object Notation) is a lightweight data interchange format. It is easy for humans to read and write. Handle JSON data into the Oracle. Oracle provides various function to store and handle JSON data in quick and easy manners:

Insert JSON data into Oracle table

-- Create a table which insert json data
create table json_test ( id number, data clob );
-- Insert the JSON formated data into the data clob column
insert into json_test values (101,'{region_id: 1, region_name: "Europe"}');
insert into json_test values (102,'{region_id: 2, region_name: "Americas"}');
insert into json_test values (103,'{region_id: 3}');
-- Access the json data in oracle
SELECT * FROM   json_test;
        ID DATA
---------- -----------------------------------------
       101 {region_id: 1, region_name: "Europe"}
       102 {region_id: 2, region_name: "Americas"}

Use of IS JSON statement

It will return data that is in JSON format and leave rows that are not in JSON format as shown below:

INSERT INTO json_test VALUES (104, 'Sunny');
SQL> select id,data from json_test where data is json;
        ID DATA
---------- ------------------------------------------
       101 {region_id: 1, region_name: "Europe"}
       102 {region_id: 2, region_name: "Americas"}
       103 {region_id: 3}

Use of JSON_VALUE function

JSON_VALUE function returns an element from the JSON data based on the specified JSON path.

col regionid for a10
col regionname for a10
SELECT JSON_VALUE(data, '$.region_id') AS regionid,
       JSON_VALUE(data, '$.region_name') AS regionname
FROM   json_test;
REGIONID   REGIONNAME
---------- ----------
1          Europe
2          Americas
SQL> insert into json_test values (103,'{region_id: 3}');
1 row created.
SQL> SELECT JSON_VALUE(data, '$.region_id') AS regionid,
            JSON_VALUE(data, '$.region_name') AS regionname
     FROM   json_test;
REGIONID   REGIONNAME
---------- ----------
1          Europe
2          Americas
3

Error handling in JSON VALUE function

Syntax: { ERROR | NULL | DEFAULT literal } ON ERROR

-- Inserted one more row which having no json data
INSERT INTO json_test VALUES (104, 'Sunny');
SELECT id,JSON_VALUE(data, '$.region_id') AS regionid,
            JSON_VALUE(data, '$.region_name') AS regionname
     FROM   json_test;
	 
        ID REGIONID   REGIONNAME
---------- ---------- ----------
       101 1          Europe
       102 2          Americas
       103 3
       104
Note: JSON_VALUE not throw error it return null if table has non json value
SELECT id,JSON_VALUE(data, '$.region_id') AS regionid,
            JSON_VALUE(data, '$.region_name' ERROR ON ERROR) AS regionname
     FROM   json_test;
ERROR:
ORA-40462: JSON_VALUE evaluated to no value

JSON_VALUE we can reset or convert the datatype as follows:

col regionid for 999
SELECT id,JSON_VALUE(data, '$.region_id' returning number) AS regionid,
            JSON_VALUE(data, '$.region_name') AS regionname
     FROM   json_test;
	 
        ID REGIONID REGIONNAME
---------- -------- ----------
       101        1 Europe
       102        2 Americas
       103        3
       104

JSON_QUERY function

JSON_QUERY function returns one or more values as shown below:

insert into json_test values (105,'{region_id: 5, region_name: { country_name: "Americas",city_name:"New York"}}');
 
SELECT id,JSON_VALUE(data, '$.region_id' returning number) AS regionid,
     JSON_VALUE(data, '$.region_name') AS regionname
     FROM   json_test;
        ID REGIONID REGIONNAME
---------- -------- ----------
       101        1 Europe
       102        2 Americas
       103        3
       104
       105        5
 
-- Json query will return and add square bracket to its output
SELECT id,
	JSON_QUERY(data, '$.region_name' WITH WRAPPER) AS country_detail
	FROM   json_test; 
 
         ID COUNTRY_DETAIL
---------- --------------------------------------------------
       101 ["Europe"]
       102 ["Americas"]
       103
       104
       105 [{"country_name":"Americas","city_name":"New York"
           }]

Use of JSON_TABLE

Represents the JSON data in the form of the table

JSON_TABLE Example
col region_id for a3
col region_name for a15
col country_name for a15
col city_name for a15
SELECT jt.region_id,
       jt.region_name,
	   jt.country_name,
	   jt.city_name
FROM   json_test,
       JSON_TABLE(data, '$'
         COLUMNS (region_id    VARCHAR2(50 CHAR) PATH '$.region_id',
                  region_name     VARCHAR2(50 CHAR) PATH '$.region_name',
                  country_name           VARCHAR2(10 CHAR) PATH '$.region_name.country_name',
                  city_name   VARCHAR2(50 CHAR) PATH '$.region_name.city_name'
                  )) jt;
				  
REG REGION_NAME     COUNTRY_NAME    CITY_NAME
--- --------------- --------------- ---------------
1   Europe
2   Americas
3
5                   Americas        New York

JSON data identify in Oracle

In Oracle, identify the list of columns using JSON data by the following view:


SELECT table_name,
       column_name,
       format,
       data_type
FROM   dba_json_columns;

Leave a Reply