JSON Relational Duality Views: Bridging Document and Relational Models

One of Oracle 23ai’s headline features is JSON Relational Duality Views. The concept is elegant: define a view once, and it automatically presents the same data as both structured relational tables and JSON documents. No duplication, no synchronization lag, no ETL.

Creating a duality view:

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW order_dv AS
SELECT JSON {
    '_id'       : o.order_id,
    'status'    : o.status,
    'customer'  : o.customer_name,
    'items'     : [
        SELECT JSON {
            'product'  : oi.product_name,
            'qty'      : oi.quantity,
            'price'    : oi.unit_price
        }
        FROM order_items oi
        WHERE oi.order_id = o.order_id
    ]
}
FROM orders o WITH INSERT UPDATE DELETE;

Reading as JSON:

SELECT data FROM order_dv WHERE data.status = 'PENDING';

Reading as relational (same data):

SELECT * FROM orders WHERE status = 'PENDING';

Both queries hit the same underlying tables. No duplication.

Why this is architecturally significant

Modern applications often have mixed workloads. A REST API might need JSON documents. Analytics queries need relational structure. Traditional solutions involve maintaining both a relational database and a document store (like MongoDB) with synchronization middleware.

Duality Views eliminate that. The same Oracle database, the same tables, the same transaction — but surfaced as JSON for your Node.js app and as SQL for your BI tool.

ORDS integration: Oracle REST Data Services (ORDS) can automatically expose duality views as REST endpoints, letting you build MongoDB-style document APIs on top of relational data with minimal code.

This is a genuinely novel architecture that deserves its own blog series. For now, the key point: if you have applications that need document APIs and relational reporting, 23ai’s duality views are worth evaluating seriously.