TREAT (column_name AS JSON) function in Oracle Database 18c

TREAT function can be used to convert the declared type to JSON. It converts the column to JSON type.

Error: we have a table with two columns and a data column containing JSON data but using the simple method to extract data from the JSON column. It gives us the following error:

SQL> select k.id,k.data.region_id,k.data.region_name from json_test k;
select k.id,k.data.region_id,k.data.region_name from json_test k
                             *
ERROR at line 1:
ORA-00904: "K"."DATA"."REGION_NAME": invalid identifier

Solution: We will use the TREAT function to treat data in JSON format. We treat it as the subquery or With clause or you can use view options

---- Use as subquery:
select k.id,k.json_data.region_id,k.json_data.region_name from (select id,treat(data as json) json_data from json_test) k;

  ID REGION_ID  REGION_NAM
---- ---------- ----------
 101 1          Europe
 102 2          Americas
 103 3
 105

-- Use with WITH 
WITH json_with_test AS (
  SELECT id,
         TREAT(data AS JSON) AS json_data
  FROM   json_test
)
SELECT k.id,
       k.json_data.region_id,
       k.json_data.region_name
FROM   json_with_test k;