8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 18c » Here

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.

Related 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:

Hope this helps. Regards Tim...

Back to the Top.