Oracle Database 23ai: JSON-Relational Duality Views and the ORA-42647 error

Contents

What is the JSON-Relational Duality Views functionality in Oracle Database 23ai, and how can it help you? What is the ORA-42647 error, and how to avoid it? Read on to find out.

Have you ever parsed complicated JSON objects into tables and had to maintain user-defined types or hardcode column names into insert using json_table? If you did, you’ll be glad to know about one of the new features in Oracle Database 23ai – JSON-Relational Duality Views. It allows you to use JSON representation of your relational model. You can create hierarchical JSON documents, modify data in tables, and more.

In this article, I will show you how I used the JSON-Relational Duality Views to insert data into tables and how to resolve 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 – each time I visit a session or read an article I feel like the technology and possibilities are getting better and better.

First test ride

I started by creating a new 23ai database in OCI and went straight to creating the duality view. Unfortunately, I encountered multiple errors which I’ll describe, so you can avoid them.

In the example, 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;

When I run it, I get the following 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”.

This was my fault – I added department_id to the employee JSON. The duality view is smart, it can resolve your foreign key from the parent object, which means the department_id column is unnecessary.

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;

However, 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”.

At first, I thought that maybe I had the wrong database version. But no, I checked, and I was definitely running 23ai. So, I Googled the error and 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? So, what gives?

When I encountered this ORA error, I looked for more examples of duality views and found an interesting one on oracle-samples GitHub. Inside, you’ll find 600 lines of code that will help you 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 ran the code, I got an error.

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

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

Start by reading the documentation!

This might confuse someone who doesn’t go straight to the documentation but uses example code from a blog article or Git repository instead. Since the _id field was introduced in version 23.4, you may find many tutorials and blog articles that don’t mention it, like I did. However, later, I found another example in a much newer Oracle document that 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 (from the same Oracle document) 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

As a side note, I did everything in the Web SQL Developer in OCI, and I just love the tool. It’s very responsive, and the Data Modeler is top-notch. If you haven’t tried it yet, do so – you won’t be disappointed.

Now, let’s go back to the duality views. 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"
 }
}

Pretty cool. What I would like to see in the future is an option to create a view that can handle all the departments and 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"
       }
     ]
   }
 ]
}

Unless I missed something and there is a way to do it, but I just haven’t figured it out. Reach out to me at tkucharzyk@preitus.com and let me know!

And that’s about it for this article! I hope you found it useful. If you’re interested in Oracle technologies, check out some of our other publications: