One of the most architecturally interesting features in Oracle 23ai is JSON Relational Duality Views. The concept is elegant: define a view that exposes your relational data as JSON documents, allow applications to read and write through that JSON interface, and have Oracle handle the translation to and from the underlying relational tables automatically. Same data, two ways to access it, zero synchronization logic.

This sounds like magic. Understanding when it’s magic and when it’s a footgun requires knowing how it actually works.

The Problem It Solves

Modern application architectures often involve:

• A mobile or web frontend that works with JSON documents
• Microservices that consume and produce JSON
• A relational Oracle database that the DBAs manage and the DBA team requires for transactional integrity, SQL reporting, and analytics

The traditional solution is a data access layer (ORM, custom mapper, API layer) that converts between the application’s JSON objects and the database’s relational tables. This layer is code. Code has bugs. Code needs maintenance. Code adds latency.

JSON Relational Duality Views eliminate this layer entirely for standard CRUD operations.

How It Works

Define a duality view over your existing tables:

sql

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW order_dv AS
SELECT JSON {
    'orderId'    : o.order_id,
    'orderDate'  : o.order_date,
    'status'     : o.status,
    'customer'   : JSON {
        'customerId' : c.customer_id,
        'name'       : c.name,
        'email'      : c.email
    },
    'lineItems'  : [
        SELECT JSON {
            'lineId'    : li.line_item_id,
            'productId' : li.product_id,
            'quantity'  : li.quantity,
            'price'     : li.unit_price
        }
        FROM order_line_items li WITH INSERT UPDATE DELETE
        WHERE li.order_id = o.order_id
    ]
}
FROM orders o WITH INSERT UPDATE DELETE
JOIN customers c ON o.customer_id = c.customer_id WITH NOINSERT NOUPDATE NODELETE;

Now an application can retrieve a complete order as a JSON document:

sql

SELECT data FROM order_dv WHERE json_value(data, '$.orderId') = 1001;

Returns:

json

{
  "orderId": 1001,
  "orderDate": "2025-04-15",
  "status": "SHIPPED",
  "customer": {
    "customerId": 42,
    "name": "Acme Corp",
    "email": "orders@acme.com"
  },
  "lineItems": [
    {"lineId": 1, "productId": 101, "quantity": 5, "price": 29.99},
    {"lineId": 2, "productId": 205, "quantity": 1, "price": 149.99}
  ]
}

The application can also insert a new order by inserting a JSON document into the duality view. Oracle decomposes it and inserts the appropriate rows into orders and order_line_items automatically. Update, delete — same pattern.

The WITH/WITHOUT Annotations — These Matter Enormously

The WITH INSERT UPDATE DELETE and NOINSERT NOUPDATE NODELETE annotations on each table in the view definition control what operations are permitted on that table through the duality view. This is not optional configuration — getting it wrong creates either a view that rejects valid operations or a view that allows dangerous mutations.

In the example above, customers is defined with NOINSERT NOUPDATE NODELETE. This means: you can read customer data as part of an order document, but you cannot create a new customer, update customer information, or delete a customer by inserting/updating/deleting through the order duality view. Customers have their own lifecycle, managed separately.

This distinction — what parts of the document are readable vs what parts are mutable — is the primary design decision when creating duality views. Think carefully about it.

ETags and Optimistic Concurrency

Every document retrieved from a duality view includes an _metadata field with an ETag:

json

{
  "_metadata": {"etag": "3A7F2B9C4D1E8A6F"},
  "orderId": 1001,
  ...
}

When you update a document, you must include the ETag. Oracle checks that the ETag still matches the current state of the underlying rows. If another process modified the data between your read and your update, the ETag won’t match and your update fails with a conflict error.

This is optimistic concurrency control — no locks held during the think time between read and update, but conflicts detected at update time. For REST API patterns, this is exactly the right model. It maps directly to HTTP ETags and If-Match headers.

sql

-- Update with ETag validation
UPDATE order_dv o
SET data = JSON_MERGEPATCH(data, '{"status": "DELIVERED"}')
WHERE json_value(data, '$.orderId') = 1001
AND json_value(data, '$._metadata.etag') = '3A7F2B9C4D1E8A6F';
-- If 0 rows updated, someone else modified it - handle the conflict

When Not to Use Duality Views

For complex analytical queries involving aggregations, window functions, or multi-table joins that don’t map to a document structure, SQL against the underlying tables is simpler and performs better.

For high-frequency, low-latency queries where JSON serialization/de-serialization overhead matters, direct relational queries are faster.

For bulk data loading where you’re inserting thousands of rows in a single operation, loading into base tables with SQL*Loader or Data Pump is more efficient.

Duality Views are the right choice for the CRUD operations of your application’s data layer — creating, reading, updating, and deleting individual or small groups of domain objects. They’re not a replacement for SQL.