JSON Relational Duality View

JSON Relational Duality Views are a special type of view. It gives you the possibility to display the relational data as a JSON document, but it also allows you to update the JSON document and then let Oracle Database populate the appropriate tables.

Relational

Let's start by creating a couple of relational tables. Again, we create the constructors, drivers and constructordrivers tables, just like we did in the Table Values Constructor blog.

create table if not exists constructors
(
 constructorid number         generated by default on null as identity
, name          varchar2( 16 )
, nationality   varchar2( 16 )
)
annotations ( purpose 'hold the constructors' );

create table if not exists drivers
(
 driverid      number         generated by default on null as identity
, driver_number number  (  2 )
, code          varchar2(  3 )
, driver_name   varchar2( 32 )
, dob           date
, nationality   varchar2( 16 )
)
annotations ( purpose 'hold the drivers' );

create table if not exists constructordrivers
(
 constructordriverid number         generated by default on null as identity
, constructorid       number
, driverid            number
)
annotations ( purpose 'register which driver is connected to which constructor' );

JSON

If we want to show this relational data in a JSON document, we can do this by creating a view like this:

create or replace view constructor_v as
select json_object
      ( key 'constructorid' value con.constructorid
      , key 'name'          value con.name
      , key 'nationality'   value con.nationality
      , key 'drivers'       value
        ( select json_arrayagg( drivers returning clob )
          from   ( select json_object
                          ( key 'condrvid' value cds.constructordriverid
                          , key 'driver'   value json_object
                                                 ( key 'driverid'      value drv.driverid
                                                 , key 'driver_number' value drv.driver_number
                                                 , key 'code'          value drv.code
                                                 , key 'driver_name'   value drv.driver_name
                                                 , key 'dob'           value drv.dob
                                                 , key 'nationality'   value drv.nationality
                                                 )
                          ) drivers
                   from   constructordrivers cds
                   join   drivers            drv
                     on   ( cds.driverid = drv.driverid )
                   where  cds.constructorid = con.constructorid
                 )
        )
      ) data
from   constructors con;

A view can be created like this in Oracle Database 19c. Especially the modern development environments expect JSON documents, and have no notion about the relational format. This way you can provide the relational data in a JSON format to the clients.

Retrieving data is one thing, but you probably also need to perform other operations on the data, like INSERT, UPDATE, or DELETE, or when you operating in the RESTful world, POST, PUT, or DELETE. This is where it gets tricky, as we will have to create custom code to perform these actions.

JSON Relational Duality View

This is where Oracle Database 23c comes to the rescue with JSON Relational Duality Views. To be able to create JSON Relational Duality views we must define Primary Keys and Foreign Keys on the tables (Of course, we should always create keys to preserve referential integrity).

create or replace json relational duality view constructor_dv as
select json { 'constructorid' : con.constructorid
           , 'name'          : con.name
           , 'nationality'   : con.nationality
           , 'drivers'       :
             [ select json { 'condrvid' : constructordriverid
                           , 'driver'   :
                             ( select json { 'driverid'      : drv.driverid
                                           , 'driver_number' : drv.driver_number
                                           , 'code'          : drv.code
                                           , 'driver_name'   : drv.driver_name
                                           , 'dob'           : drv.dob
                                           , 'nationality'   : drv.nationality
                                           }
                               from   drivers drv with update
                               where  cds.driverid = drv.driverid
                             )
                           }
               from   constructordrivers cds with insert update delete
               where  con.constructorid = cds.constructorid
             ]
           }
from   constructors con with insert update delete;

This document looks almost exactly the same as the one that is created by the normal view, except for the '_metadata' tag. The data in this tag is used by Oracle Database to perform its optimistic locking and to make sure other sessions changes aren't overwritten.

GraphQL Format

But to demonstrate how we can leverage the power of JSON Relational Duality Views, we create another duality view, just on the drivers table, this time using GraphQL format.

create or replace json relational duality view simpledriver_dv as
drivers @insert @update @delete
{ driverid      : driverid
, driver_number : driver_number
, code          : code
, driver_name   : driver_name
, dob           : dob
, nationality   : nationality
};

RESTful Services

What's the use of supplying JSON documents when we are issuing SQL statements anyway? Would it not make more sense to just update the relational tables and have the client retrieve the updated JSON documents? Well, if we REST enable our database, we can just issue PUT and POST commands directly against the database with a JSON document as the payload and have Oracle Database do all the heavy lifting of putting all the data in the correct tables.

declare
 pragma autonomous_transaction;
begin
 ords.enable_schema
   ( p_enabled             => true
   , p_schema              => 'DEMO'
   , p_url_mapping_type    => 'BASE_PATH'
   , p_url_mapping_pattern => 'demo'
   , p_auto_rest_auth      => false
   );
 commit;
end;

Conclusion

JSON Relational Duality gives you the best of both worlds. Relational experts can work on the same data as Document database experts without learning a new trade. App developers will love Duality views as they can request any JSON document layout they want. In addition, database development becomes simpler as the database developers do not have to deal with time-consuming ORM mapping. Oracle made a massive leap over other Databases with this new feature.