8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
TREAT(... AS JSON) in Oracle Database 18c
In Oracle 18c the TREAT
function can be used in SQL to change the declared type of an expression to JSON, where the expression is an object containing JSON data.
- Setup
- Without the TREAT(... AS JSON) Function
- TREAT(... AS JSON) Function - Inline View
- TREAT(... AS JSON) Function - WITH Clause
- TREAT(... AS JSON) Function - View
- TREAT(... AS JSON) Function - Error Handling
Related articles.
- JSON Support Enhancements in Oracle Database 18c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Setup
Create a test table containing some JSON data. Notice there is no IS JSON
constraint on the column holding the JSON data.
--DROP TABLE json_documents PURGE; CREATE TABLE json_documents ( id RAW(16) NOT NULL, data CLOB, CONSTRAINT json_documents_pk PRIMARY KEY (id) ); INSERT INTO json_documents VALUES (SYS_GUID(), '{"name":"banana", "quantity":10}'); INSERT INTO json_documents VALUES (SYS_GUID(), '{"name":"apple", "quantity":5}'); INSERT INTO json_documents VALUES (SYS_GUID(), '{"name":"orange", "quantity":7}'); COMMIT;
Without the TREAT(... AS JSON) Function
Without the IS JSON
constraint on the column, if we try to query the column and traverse the JSON data inside it using dot notation the query fails, as the data is not seen as JSON data by SQL.
SELECT j.data.name, j.data.quantity FROM json_documents j ORDER BY 1; j.data.quantity * ERROR at line 2: ORA-00904: "J"."DATA"."QUANTITY": invalid identifier SQL>
TREAT(... AS JSON) Function - Inline View
In the following example we use the TREAT(... AS JSON)
function inside an inline view to tell SQL the contents of the column is JSON. This allows the outer query to use dot notation, even though the original column doesn't have the IS JSON
check constraint.
COLUMN name FORMAT A15 COLUMN quantity FORMAT A15 SELECT jd.id, jd.json_data.name, jd.json_data.quantity FROM (SELECT id, TREAT(data AS JSON) AS json_data FROM json_documents) jd ORDER BY 2; ID NAME QUANTITY -------------------------------- --------------- --------------- 6943671DB33E4A21E05336BB1C0A4E06 apple 5 6943671DB33D4A21E05336BB1C0A4E06 banana 10 6943671DB33F4A21E05336BB1C0A4E06 orange 7 SQL>
TREAT(... AS JSON) Function - WITH Clause
Alternatively, we could use the TREAT(... AS JSON)
function inside the WITH
clause instead of an inline view with the same results.
WITH json_docs AS ( SELECT id, TREAT(data AS JSON) AS json_data FROM json_documents ) SELECT jd.id, jd.json_data.name, jd.json_data.quantity FROM json_docs jd ORDER BY 2; ID NAME QUANTITY -------------------------------- --------------- --------------- 6943671DB33E4A21E05336BB1C0A4E06 apple 5 6943671DB33D4A21E05336BB1C0A4E06 banana 10 6943671DB33F4A21E05336BB1C0A4E06 orange 7 SQL>
TREAT(... AS JSON) Function - View
The TREAT(... AS JSON)
function works equally well in a view.
CREATE OR REPLACE VIEW json_documents_v AS SELECT id, data, TREAT(data AS JSON) AS json_data FROM json_documents; SELECT jd.id, jd.json_data.name, jd.json_data.quantity FROM json_documents_v jd ORDER BY 2; ID NAME QUANTITY -------------------------------- --------------- --------------- 6943671DB33E4A21E05336BB1C0A4E06 apple 5 6943671DB33D4A21E05336BB1C0A4E06 banana 10 6943671DB33F4A21E05336BB1C0A4E06 orange 7 SQL>
TREAT(... AS JSON) Function - Error Handling
The TREAT(... AS JSON)
function doesn't validate the contents of the input expression to check it is valid JSON. To show this insert some non-JSON data into the test table and query the table contents using the TREAT(... AS JSON)
function.
INSERT INTO json_documents VALUES (SYS_GUID(), 'name:mango, quantity:3'); COMMIT; COLUMN json_data FORMAT A35 SELECT id, TREAT(data AS JSON) AS json_data FROM json_documents; ID JSON_DATA -------------------------------- ----------------------------------- 694F6EFA628D2A8EE05336BB1C0A442D name:mango, quantity:3 6943671DB33D4A21E05336BB1C0A4E06 {"name":"banana", "quantity":10} 6943671DB33E4A21E05336BB1C0A4E06 {"name":"apple", "quantity":5} 6943671DB33F4A21E05336BB1C0A4E06 {"name":"orange", "quantity":7} SQL>
If we query the data as we did before we will see NULL values returned by dot notation for this new row.
COLUMN name FORMAT A15 COLUMN quantity FORMAT A15 SELECT jd.id, jd.json_data.name, jd.json_data.quantity FROM (SELECT id, TREAT(data AS JSON) AS json_data FROM json_documents) jd ORDER BY 2; ID NAME QUANTITY -------------------------------- --------------- --------------- 6943671DB33E4A21E05336BB1C0A4E06 apple 5 6943671DB33D4A21E05336BB1C0A4E06 banana 10 6943671DB33F4A21E05336BB1C0A4E06 orange 7 694F6EFA628D2A8EE05336BB1C0A442D SQL>
If you don't like this, limit the data selected to those rows containing valid JSON data using the IS JSON
condition.
SELECT jd.id, jd.json_data.name, jd.json_data.quantity FROM (SELECT id, TREAT(data AS JSON) AS json_data FROM json_documents WHERE data IS JSON) jd ORDER BY 2; ID NAME QUANTITY -------------------------------- --------------- --------------- 6943671DB33E4A21E05336BB1C0A4E06 apple 5 6943671DB33D4A21E05336BB1C0A4E06 banana 10 6943671DB33F4A21E05336BB1C0A4E06 orange 7 SQL>
For more information see:
- TREAT
- JSON Support Enhancements in Oracle Database 18c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Hope this helps. Regards Tim...