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.
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.
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.
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.
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:
