Exploring Oracle Database 23ai: A First Look at JSON-Relational Duality Views

One of the new features in Oracle Database 23ai is JSON-Relational Duality Views that will allow you to use JSON representation of your relational model. You can create hierarchical JSON documents and use it to modify data in tables and many more.

If you ever parsed some complicated JSON objects into tables and had to maintain user-defined types or hardcode column names into insert using json_table, you know that this may be a big change for you.

In this article, I will describe how I tried to use the JSON-Relational Duality Views to insert the data into tables and also how to resolve some problems I encountered during my first test of the feature.

The idea

I got the idea to try this new feature out when I saw an example from Chris Saxon on LinkedIn (gif below). It looked like this feature may cure my JSON headache, so I had to try it out. Big thanks to Chris for sending me the GIF!

Recently I had the opportunity to see Chris on a live session about Simple Data Definitions with Domains and Annotations on Oracle user Group Norway (OUGN) 2024, and I am very proud to be a part of the Oracle community because every time I visit a session or read an article I feel like the technology and possibilities are getting better and better.

First test ride

After I saw the post I immediately created a new 23ai database in OCI and went straight to creating the duality view.

Unfortunately, I hit multiple errors that I would like to describe, so you can avoid them.

In the example that I got inspired with, Chris created a duality view for the hierarchy of departments and employees table. The JSON structure was defined with department_id, a few more attributes, and then an array of employees.

I tried to do the same and created very simple departments and employees

When I created the duality view I got an ORA-40609 error right away. This is what my duality view looks like.

create or replace json relational duality view department_employees
as
select json {
   'department_id' : d.department_id
   ,'department_name' : d.dname
   ,'employees' : [
       (
           select json {
               'employee_id' : e.employee_id
               ,'employee_name' : e.name
               ,'department_id' : e.department_id
           }
           from employees e with insert update delete
           where e.department_id = d.department_id
       )
   ]}
from departments d with insert update delete;

And when I run it, I get this error.

ORA-40609: Cannot create JSON Relational Duality View 'DEPARTMENT_EMPLOYEES': foreign key column 'DEPARTMENT_ID' of table 'EMPLOYEES' should be part of the primary or unique key of the same table, or it should not be selected to construct subobject 'employees'. Error at Line: 7 Column: 0

Well, this was my fault, I added department_id into employee JSON, but the duality view is so smart, that it can resolve your foreign key from the parent object. It is then unnecessary to add the department_id column.

So I removed it and hit the run button again. This is how the view looked like for the second attempt.

create or replace json relational duality view department_employees
as
select json {
   'department_id' : d.department_id
   ,'department_name' : d.dname
   ,'employees' : [
       (
           select json {
               'employee_id' : e.employee_id
               ,'employee_name' : e.name
           }
           from employees e with insert update delete
           where e.department_id = d.department_id
       )
   ]}
from departments d with insert update delete;

But when I ran it, I got another error.

ORA-42647: Missing '_id' field at the root level for JSON-relational duality view 'DEPARTMENT_EMPLOYEES'. Error at Line: 7 Column: 0

First I thought that maybe I had the wrong version of the database. But no, I am running 23ai.

So I googled the error and I found a pretty straightforward answer. Just add the ‘_id’ at the root level of the duality view.

But I already had a ‘department_id’ right?

When I hit this ORA error, I looked for some more examples of duality views and I found an interesting one in oracle-samples GitHub.

Oracle-Samples / Duality View Tutorial

There are 600 lines of code that will help you to create JSON Relational Duality Views based on Formula 1 data tables like race, team, or driver. This is what one of the views looks like.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW race_dv AS
 SELECT JSON {'raceId' : r.race_id,
              'name'   : r.name,
              'laps'   : r.laps WITH NOUPDATE,
              'date'   : r.race_date,
              'podium' : r.podium WITH NOCHECK,
              'result' :
                [ SELECT JSON {'driverRaceMapId' : drm.driver_race_map_id,
                               'position'        : drm.position,
                               UNNEST
                                 (SELECT JSON {'driverId' : d.driver_id,
                                               'name'     : d.name}
                                    FROM driver d WITH NOINSERT UPDATE NODELETE
                                    WHERE d.driver_id = drm.driver_id)}
                    FROM driver_race_map drm WITH INSERT UPDATE DELETE
                    WHERE drm.race_id = r.race_id ]}
   FROM race r WITH INSERT UPDATE DELETE;

And of course, when I run the code this is what I got.

ORA-42647: Missing'_id' field at the root level for JSON-relational duality view 'RACE_DV'. Error at Line: 7 Column: 0

So I will save you time and tell you how to solve this problem.

Read the documentation!

This might be very confusing for someone who doesn't go straight to the documentation but instead uses example code from a blog or git repository. As the '_id' field was introduced in version 23.4., so you may find tutorials and blogs describing it without it.

Later I found another example in a much newer Oracle document
Introduction To Car-Racing Duality Views Example

The document explicitly says:

“ A document supported by a duality view always includes, at its top (root) level, a document-identifier field, _id, that corresponds to the primary-key columns of the tables that underlie the view "

The example below also looks a little bit different, right?

{
  "_id": 302,
  "name": "Ferrari",
  "points": 300,
  "driver": [
    {
      "driverId": 103,
      "name": "Charles Leclerc",
      "points": 192
    },
    {
      "driverId": 104,
      "name": "Carlos Sainz Jr",
      "points": 118
    }
  ]
}

So now we know that the ‘_id’ attribute is not my department_id. It has to be an “_id” attribute. I fixed it and the view was successfully created.

Results

By the way, I did everything in the Web SQL Developer in OCI and I just love the tool. It is very responsive and the Data Modeler is top-notch. If you have not tried it yet, you have to do it.

The last thing I had to do was the actual insert.

insert into department_employees
values (
   '{
       "_id" : 1
       ,"department_name" : "HR"
       ,"employees" : [
           {
               "employee_id" : 1
               ,"employee_name" : "John"
           }
           ,{
               "employee_id" : 2
               ,"employee_name" : "Jane"
           }
       ]
   }'
);

And now when I select the data using my JSON-Relational Duality View department_employees. It will give me hierarchical JSON right away.

{
 "_id": 1,
 "department_name": "HR",
 "employees": [
   {
     "employee_id": 1,
     "employee_name": "John"
   },
   {
     "employee_id": 2,
     "employee_name": "Jane"
   }
 ],
 "_metadata": {
   "etag": "A8157B69FD499CFB78E02ADB8EEAC553",
   "asof": "0000258E29725EBD"
 }
}

What I would like to see is an option to create a view that can handle all the departments, that will be able to insert multiple objects into the root level. Something like the example below.

{
 "departments": [
   {
     "department_id": 1,
     "department_name": "HR",
     "employees": [
       {
         "employee_id": 1,
         "employee_name": "John"
       },
       {
         "employee_id": 2,
         "employee_name": "Jane"
       }
     ]
   },
   {
     "department_id": 2,
     "department_name": "IT",
     "employees": [
       {
         "employee_id": 3,
         "employee_name": "Doe"
       },
       {
         "employee_id": 4,
         "employee_name": "Smith"
       }
     ]
   }
 ]
}

But maybe there is a way to do it, I just haven't figured it out. Let me know in the comments if you know how to do it!

I hope you will find this article useful. Thank you for reading it till the end.