One Database, Two Worlds: Getting Started with JSON Relational Duality Views in Oracle Database 26ai
Oracle Database has alwakys been excellent at managing relational data. The interesting part is that modern applications don't always want relational data - they usually want JSON. That's where JSON Relational Duality Views become interesting...
The challenge is that modern applications don’t always think in terms of rows and joins.
REST APIs, mobile apps, and JavaScript front ends usually expect JSON documents, not multiple result sets stitched together from several tables.
For a long time, that meant writing fairly complex SQL using JSON_OBJECT() and JSON_ARRAYAGG(), or maintaining a separate document model alongside the relational one.
Neither approach felt great.
With JSON Relational Duality Views, Oracle takes a different path. Instead of forcing you to choose between relational and document models, it lets you work with both—without duplicating your data.
Let’s take a look at how that works.
The Result We're Looking For
Imagine an application requesting information about a server.
Instead of joining multiple tables and manually building a JSON response, the application simply gets something like this:
{
"_id": 1,
"server_name": "APP-SRV-01",
"region": "Canada",
"services": [
{
"service_name": "Oracle REST Data Services",
"status": "Running"
},
{
"service_name": "Oracle APEX",
"status": "Running"
}
]
}
What’s interesting here is that this document isn’t stored anywhere.
Oracle generates it on the fly from relational tables.
Step 1 – Create the Relational Model
We’ll keep things simple for this example.
Our database has two tables:
create table servers
(
server_id number primary key,
server_name varchar2(50),
region varchar2(30)
);
create table server_services
(
service_id number primary key,
server_id number references servers(server_id),
service_name varchar2(60),
status varchar2(20)
);
Nothing fancy here—just a straightforward relational design.
Step 2 – Insert Some Sample Data
insert into servers
values
(
1,
'APP-SRV-01',
'Canada'
);
insert into servers
values
(
2,
'APP-SRV-02',
'Germany'
);
insert into server_services
values
(
1,
1,
'Oracle REST Data Services',
'Running'
);
insert into server_services
values
(
2,
1,
'Oracle APEX',
'Running'
);
insert into server_services
values
(
3,
2,
'Oracle Database',
'Running'
);
commit;
At this point, everything is still purely relational.
Step 3 – Create a JSON Relational Duality View
This is where things start to get interesting.
A Duality View defines how Oracle should present those relational tables as a JSON document.
The full statement can get quite long depending on your model, so I won’t go into every detail here.
Conceptually, it looks something like this:
CREATE JSON RELATIONAL DUALITY VIEW server_overview AS ...
For our example, the Duality View could look like this:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW server_overview AS
SELECT JSON
{
'_id' : s.server_id,
'serverName' : s.server_name,
'region' : s.region,
'services' :
[
SELECT JSON
{
'serviceId' : ss.service_id,
'serviceName' : ss.service_name,
'status' : ss.status
}
FROM server_services ss
WHERE ss.server_id = s.server_id
]
}
FROM servers s;
Immediately, Oracle understands that one server owns multiple services and automatically produces a nested JSON document.
No JSON_OBJECT().
No JSON_ARRAYAGG().
No manual formatting.
Just a declarative definition of how the relational model should appear as JSON.
Step 4 – Query the View
Querying the Duality View feels very natural:
select json_serialize(data pretty)
from server_overview;
Result:
{
"_id":1,
"serverName":"APP-SRV-01",
"region":"Canada",
"services":[
{
"serviceId":1,
"serviceName":"Oracle REST Data Services",
"status":"Running"
},
{
"serviceId":2,
"serviceName":"Oracle APEX",
"status":"Running"
}
]
}
Instead of multiple joined rows, you get a clean, structured JSON document that represents the full business object.
This means your APIs or applications can work with hierarchical JSON, while the database continues to store normalized relational data underneath.
Why Is This Different?
Before Duality Views, generating JSON usually involved writing SQL like this:
Multiple joins
Nested
JSON_OBJECT()callsJSON_ARRAYAGG()A fair amount of formatting logic
With Duality Views, Oracle takes care of all of that for you.
| Before | With Duality Views |
|---|---|
| Multiple JOINs | One JSON document |
| Manual JSON generation | Automatic JSON representation |
| Separate relational and document models | One source of truth |
| Synchronization between models | No synchronization required |
The relational model doesn't change.
You're simply exposing it in a different way.
Where This Fits in Oracle APEX
This is where things get especially interesting, at least from my perspective.
If you’re building an Oracle APEX application, your Interactive Reports, Interactive Grids, and SQL processes can continue working with the relational tables you already have.
At the same time, your REST APIs can expose those same business objects as hierarchical JSON using a Duality View.
No duplicate tables.
No extra synchronization.
No scattered JSON-building logic across your application.
The same data serves both relational and document-based use cases.
Things to Keep in Mind
Like any feature, Duality Views aren't the answer to every problem.
A few things to keep in mind:
They require Oracle Database 23ai or newer.
Your relational model should have well-defined primary and foreign keys.
A clean relational design still matters.
They simplify JSON consumption—they don't replace good database design.
Final Thoughts
When I first heard about JSON Relational Duality Views, I thought they were just another JSON-related feature.
After spending some time with them, I see them differently.
They change how you can think about building applications.
For years, developers often had to choose between relational databases and document databases.
Oracle is now making that decision a little less important.
Your data can stay relational, while applications interact with it as if it were a document.
If you’re building REST services, Oracle APEX apps, or modern integrations, that’s a pretty powerful idea—and definitely worth exploring.
💡 Tip
If you’re already using Oracle APEX with ORDS, consider exposing complex business objects through JSON Relational Duality Views instead of manually building JSON responses. It keeps your relational model clean, makes your APIs easier to maintain, and avoids having multiple versions of the same data.
Next Steps
In this article, we focused more on the concept than on every detail of the syntax.
In a future post, I’ll walk through how to build a complete JSON Relational Duality View from scratch, update data through the JSON document, and consume it directly from an Oracle APEX application using ORDS.
