JSON-Relational Duality Views
in Oracle APEX
The complete guide to bridging relational tables and JSON documents storing data efficiently, accessing it flexibly.
📋 Table of Contents
1What Are Duality Views?
In one of my Oracle APEX projects, I had a requirement to expose relational data as JSON for integration with external systems. The application needed to support both database transactions and API-based data exchange, which made the design slightly complex. Initially, I considered building REST APIs with manual JSON construction using SQL functions. However, maintaining that logic became difficult as the data model evolved. That’s when I explored JSON-Relational Duality Views — a feature that allows working with relational data and JSON seamlessly.
data that you can use to read and write information from one or more underlying relational tables — simultaneously.2Why We Needed Duality Views
In my experience, I often faced confusion about how to store and use data — whether to keep it in relational tables or convert it into JSON for modern applications. Relational tables are good for maintaining data properly, but when it comes to APIs or frontend usage, JSON format is much easier to work with. Earlier, I had to handle both separately — storing data in tables and then writing extra logic to convert it into JSON. This made the process more complex and time-consuming. With Duality Views, this problem got solved for me. I can now work with the same data in both relational and JSON formats without writing additional conversion logic.
| Approach | Storage | App Access | Problem |
|---|---|---|---|
| Pure Relational | Normalized tables | SQL JOINs | Complex JOINs; hard for modern APIs |
| Pure NoSQL / JSON | JSON documents | Document API | Data duplication, no normalization |
| ✅ Duality Views | Relational tables | JSON documents | Best of both — zero trade-offs |
3Prerequisites
In Oracle APEX, a Duality View Source consists of:
- The owner and name of the Duality View object in Oracle Database
- A Data Profile — automatically built by APEX after reading the JSON schema from the view
- The Data Profile maps JSON attributes to typed columns that APEX components can consume directly
4Practical Example: Employee-Department App
Let's walk through a complete real-world example — an Employee Directory that exposes nested department data as clean JSON documents, while storing everything in two normalized tables.
Step 1 — Create the Relational Tables
Start with plain, standard Oracle tables. Nothing special needed here:
-- Department master table CREATE TABLE dept ( deptno NUMBER PRIMARY KEY, dname VARCHAR2(50), loc VARCHAR2(50) ); -- Employee table with FK to dept CREATE TABLE emp ( empno NUMBER PRIMARY KEY, ename VARCHAR2(50), job VARCHAR2(30), sal NUMBER, deptno NUMBER REFERENCES dept(deptno) ); -- Sample data INSERT INTO dept VALUES (10, 'ACCOUNTING', 'CHENNAI'); INSERT INTO dept VALUES (20, 'RESEARCH', 'BANGALORE'); INSERT INTO emp VALUES (101, 'Ravi', 'ANALYST', 75000, 20); INSERT INTO emp VALUES (102, 'Priya', 'MANAGER', 90000, 10); INSERT INTO emp VALUES (103, 'Arjun', 'DEVELOPER', 65000, 20); COMMIT;
Step 2 — Create the Duality View
Create the Duality View that exposes each employee as a nested JSON document with department info — from two tables, in one view:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW emp_dept_dv AS SELECT JSON { '_id' : e.empno, 'name' : e.ename, 'job' : e.job, 'salary' : e.sal, 'department' : ( SELECT JSON { 'deptno' : d.deptno, 'dname' : d.dname, 'loc' : d.loc } FROM dept d WITH NOINSERT NOUPDATE NODELETE WHERE d.deptno = e.deptno ) } FROM emp e WITH INSERT UPDATE DELETE;
WITH INSERT UPDATE DELETE on emp allows full CRUD. WITH NOINSERT NOUPDATE NODELETE on dept makes it read-only through this view. Fine-grained table-level control!Step 3 — Query It Like JSON
-- Read all employees as JSON documents SELECT * FROM emp_dept_dv; -- Find a specific employee SELECT e.data FROM emp_dept_dv e WHERE e.data.name.string() = 'Ravi';
The output is a beautifully structured JSON document:
"_id": 101,
"name": "Ravi",
"job": "ANALYST",
"salary": 75000,
"department": {
"deptno": 20,
"dname": "RESEARCH",
"loc": "BANGALORE"
}
}
Two relational tables. One clean JSON document. Zero manual serialization code. That is the power of Duality Views.
5Configuring Duality Views in Oracle APEX
After creating the Duality View in the database, connecting it to your APEX application takes just a few clicks:
6CRUD Operations via Duality Views
Duality Views support full Create, Read, Update, and Delete using JSON. Every operation automatically maps to the correct underlying relational tables — no separate DML per table needed.
INSERT — Add a New Employee
INSERT INTO emp_dept_dv VALUES ( '{"_id":104,"name":"Meena","job":"DBA", "salary":80000,"department":{"deptno":10}}' ); -- Oracle automatically inserts into the EMP table
UPDATE — Modify Salary
UPDATE emp_dept_dv e SET e.data = JSON_TRANSFORM(e.data, SET '$.salary' = 85000) WHERE e.data._id = 104; -- Updates SAL column in EMP table automatically
DELETE — Remove an Employee
DELETE FROM emp_dept_dv e WHERE e.data._id = 104; -- Deletes from EMP table. DEPT table untouched (NODELETE).
7Key Benefits Summary
🔵 For DBAs
Data stays perfectly normalized. No schema changes to existing tables. Full SQL analytics still work.
🟢 For Developers
Work with clean JSON documents. No ORM configuration. One API call fetches a complete nested object.
🟡 For Architects
One database. One source of truth. Support legacy SQL apps and modern JSON APIs simultaneously.
🟣 For APEX Devs
Configure as a Shared Component data source. Build reports and forms with zero PL/SQL boilerplate.
The Future of Oracle App Development is Dual
JSON-Relational Duality Views are not just a feature — they are a fundamental shift in how Oracle applications are built. Store once, access everywhere, in any format your application needs.
Comments
Post a Comment