JSON Duality View in Oracle 23 ai.

 

Oracle 23ai introduced "JSON-Relational Duality" Views. 

With this feature , the existing relational structure can be retrieved 

as a JSON document and the data can be stored as JSON documents.

With this approach , the application can use "elastic schema" .

Let us jump into a simple use case / example. 

Here is the script for table / record creation.

CREATE TABLE dept (

    depid    INT PRIMARY KEY,

    deptname VARCHAR2(50) NOT NULL

);

CREATE TABLE emp (

    empid   INT PRIMARY KEY,

    empname VARCHAR2(50) NOT NULL,

    depid   INT,

    CONSTRAINT dep_fk FOREIGN KEY ( depid )

        REFERENCES dept ( depid )

);

insert into dept values ( 1 , 'HR');

insert into dept values ( 2 , 'DEVOPS');

insert into dept values ( 3 , 'RETAIL');

Insert into emp values ( 101 , 'Zahir Mohideen' ,1 );

Insert into emp values ( 201 , 'John' , 2);

Insert into emp values ( 202 , 'Jane' , 2);

Let us create a  simple JSON document from the "emp" table. 

Secondly , we can create another JSON document by joining the "dept" and "emp" table.  The following JSON document is "schema flexible" or "elastic schema" to suit the application's needs .  That is , in the example , "HR" has one employee and "DEVOPS" have two employees and the "RETAIL" has none.

 

When  the above SQL is used to create  JSON duality view , Oracle greets us with the error.

According to the oracle documentation ( https://docs.oracle.com/en/database/oracle/oracle-database/23/jsnvu/document-identifier-field-duality-views.html#GUID-A1445407-623E-4898-BE32-5789A11E2EBD) , duality view always include "document identifier" field , "_id" ...  So , let us rewrite our view as below.

Yikes . ... We can't use alias . 

So , let us try it for the 3rd time . 

Now , we got it . 

Comments Welcome!