JSON Generation Enhancements in Oracle Database 18c

View All Scripts Login to Run Script

Script Name JSON Generation Enhancements in Oracle Database 18c
Description This script showcases the enhancements that have been to the JSON Generation operators in Oracle 18c. JSON_OBJECT, JSON_ARRAY, JSON_ARRAYAGG and JSON_OBJECTAGG now support returning CLOB and BLOB datatypes. This allows much larger JSON documents to be generated using these operators, allowing a single simple SQL statement to be used to generate very large JSON documents.
Area SQL General / JSON
Contributor Oracle
Created Wednesday February 14, 2018

Statement 1
It is expected behavior for this this statement to raise ORA-00942 is this is the first time the script has been run in this session.
Drop the table if it already exists
```
drop table JSON_EXAMPLE1 
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942
Statement 2
Note that this table does not have the IS JSON check constraint applied to JSON_COLUMN. Oracle recommends that columns that will contain JSON data should always use a check constraint to ensure that the content of the column is valid JSON. Such a constraint would be added by appendng the clause CHECK (JSON_COLUMN IS JSON) to the definition of the table.
Create a table to contain JSON data.
```
create table JSON_EXAMPLE1(  
   PKEY NUMBER(2),  
   JSON_COLUMN VARCHAR2(64)  
) 
```
```
Table created.
```
Statement 3
Insert a simple JSON document consisting of an object with 2 key value pairs into the table
Insert Data
```
insert into JSON_EXAMPLE1 values (1, '{ "MyKey1" : "Value1", "MyKey2" : "Value2" }') 
```
```
1 row(s) inserted.
```
Statement 4
Commit the insert operation.
Commit
```
commit 
```
```
Statement processed.
```
Statement 5
Oracle's "Simplified Syntax for JSON" can only be used with column that have an IS JSON constraint applied to them. If you attempt to use the simplified syntax on a column that does not have an IS JSON constraint the SQL processor does not know how to interpret the JSON path references and raises "ORA-00904: invalid identifier".
Using Simplified Syntax for JSON on a column that does have an IS JSON constraint
```
select PKEY, ex.JSON_COLUMN.MyKey1, ex.JSON_COLUMN.MyKey2   
  from JSON_EXAMPLE1 ex 
```
```
ORA-00904: "EX"."JSON_COLUMN"."MYKEY2": invalid identifier
```
More Details: https://docs.oracle.com/error-help/db/ora-00904
Statement 6
Oracle 18c introduces a new variant of the TREAT operator that can be used to mark content as JSON. Once the content is marked as JSON simplified syntax can use be used to query the JSON content. In this example we are using a WITH clause to mark the content of the column as JSON.
Using TREAT(... AS JSON) in directly in SQL to mark content as JSON
```
with MY_JSON_TABLE as  
(  
   select PKEY, TREAT(JSON_COLUMN as JSON) JSON_COLUMN  
     from JSON_EXAMPLE1  
)  
select PKEY, j.JSON_COLUMN.MyKey1, j.JSON_COLUMN.MyKey2  
  from MY_JSON_TABLE j 
```
```
PKEY MYKEY1 MYKEY2
1 Value1 Value2
```

PKEY	MYKEY1	MYKEY2
1	Value1	Value2

Statement 7

In this example we are creating a view based on TREAT(... as JSON).

Create a view based on TREAT(... as JSON)

create or replace view JSON_ENABLED_VIEW  
as   
select TREAT(JSON_COLUMN as JSON) JSON_COLUMN  
 from JSON_EXAMPLE1

View created.

Statement 8
We can now use simplified syntax to query the view, even thought the column in the base table does not have an IS JSON constraint.
Using Simplified syntax against a View with JSON content
```
select j.JSON_COLUMN.MyKey1,j.JSON_COLUMN.MyKey2  
  from JSON_ENABLED_VIEW j 
```
```
MYKEY1 MYKEY2
Value1 Value2
```
Statement 9
This statement generates a JSON object containing a single key/value pair. The name of the key is "MyObject" and it's value comes from the column JSON_COLUMN. Although the column contains JSON, the JSON_OBJECT operator treats it as a string when generating JSON.
Generating JSON OBJECT from a column containing JSON
```
select JSON_OBJECT('MyObject' value JSON_COLUMN) JSON  
  from JSON_EXAMPLE1 
```
```
JSON
{"MyObject":"{ \"MyKey1\" : \"Value1\", \"MyKey2\" : \"Value2\" }"}
```
Statement 10
By enclosing the JSON_COLUMN in a TREAT(JSON_COLUMN as JSON) operator we can force JSON_OBJECT to correctly embedded the content of JSON_COLUMN as an object.
Add a TREAT(... AS JSON) Operator to handle the content correctly
```
select PKEY, JSON_OBJECT('MyObject' value TREAT(JSON_COLUMN as JSON)) JSON  
  from JSON_EXAMPLE1 
```
```
PKEY JSON
1 {"MyObject":{ "MyKey1" : "Value1", "MyKey2" : "Value2" }}
```

MYKEY1	MYKEY2
Value1	Value2

JSON
{"MyObject":"{ \"MyKey1\" : \"Value1\", \"MyKey2\" : \"Value2\" }"}

PKEY	JSON
1	{"MyObject":{ "MyKey1" : "Value1", "MyKey2" : "Value2" }}

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database