JSON Relational Duality Views in Oracle Database 26ai: The Best of Both Worlds

/ 26ai New Features / By

Introduction

For decades, developers have been forced to choose between the structural integrity of relational databases and the flexibility of document-store models. JSON Relational Duality Views in Oracle Database 26ai eliminate this tradeoff entirely. They allow you to access and manipulate fully normalized relational data as nested JSON documents, and vice versa, without any data duplication.

Application developers get the intuitive, schema-flexible JSON documents they love. DBAs retain fully normalized schemas with referential integrity, ACID transactions, and efficient storage. Everyone wins.

What Makes 26ai’s Duality Views Special?

  • GraphQL-style syntax for defining duality views that map normalized tables into nested JSON documents.
  • Automatic ETag-based optimistic concurrency control – no application-side locking logic needed.
  • Full DML support – INSERT, UPDATE, and DELETE on the JSON document automatically cascade to underlying relational tables with ACID guarantees.
  • Seamless REST and MongoDB-compatible driver integration for modern microservices architectures.

Setting Up the Relational Schema

Let’s start with a classic normalized schema: customers, orders, and order items.

CREATE TABLE customers (
    customer_id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name          VARCHAR2(100) NOT NULL,
    email         VARCHAR2(200) NOT NULL UNIQUE
);

CREATE TABLE orders (
    order_id      NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id   NUMBER NOT NULL REFERENCES customers(customer_id),
    order_date    DATE DEFAULT SYSDATE,
    status        VARCHAR2(20) DEFAULT 'PENDING'
);

CREATE TABLE order_items (
    item_id       NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id      NUMBER NOT NULL REFERENCES orders(order_id),
    product_name  VARCHAR2(200) NOT NULL,
    quantity      NUMBER NOT NULL,
    unit_price    NUMBER(10,2) NOT NULL
);

Three normalized tables with proper foreign keys – exactly what your DBA wants to see.

Creating a JSON Relational Duality View

Now we define a duality view using Oracle 26ai’s GraphQL-like syntax. This maps the normalized structure into a nested JSON document centered on the customer.

CREATE JSON RELATIONAL DUALITY VIEW customer_orders_dv AS
customers @insert @update @delete {
    _id        : customer_id
    name       : name
    email      : email
    orders     : orders @insert @update @delete {
        order_id   : order_id
        order_date : order_date
        status     : status
        items      : order_items @insert @update @delete {
            item_id      : item_id
            product_name : product_name
            quantity     : quantity
            unit_price   : unit_price
        }
    }
};

The @insert @update @delete annotations grant full DML capabilities at each level. The result is a single JSON document that spans three tables – readable and writable.

Inserting Data Through the Duality View

Insert a complete customer with orders and line items in one JSON document:

INSERT INTO customer_orders_dv VALUES (
    '{
        "name": "Ahmed Baraka",
        "email": "ahmed@ahmedbaraka.com",
        "orders": [
            {
                "order_date": "2025-07-15",
                "status": "CONFIRMED",
                "items": [
                    {"product_name": "Oracle 26ai License", "quantity": 1, "unit_price": 5000.00},
                    {"product_name": "Support Plan", "quantity": 1, "unit_price": 1200.00}
                ]
            }
        ]
    }'
);

One INSERT statement. Behind the scenes, Oracle populates all three relational tables, generates identity keys, and enforces referential integrity – all within a single ACID transaction.

Querying and Updating with ETags

Query the duality view to retrieve a full document with its auto-generated ETag:

SELECT json_serialize(data PRETTY)
FROM   customer_orders_dv d
WHERE  d.data.name = 'Ahmed Baraka';

The returned JSON includes an "_metadata": {"etag": "..."} field. Use this ETag for safe concurrent updates – Oracle will reject the operation if the underlying data has changed since you read it:

UPDATE customer_orders_dv d
SET    data = json_mergepatch(
           data,
           '{"orders": [{"order_id": 1, "status": "SHIPPED"}]}'
       )
WHERE  d.data."_id" = 1
AND    json_value(data, '$._metadata.etag') = 'your-etag-value-here';

This optimistic concurrency control is automatic – no custom locking logic required, making it perfect for stateless REST APIs and microservices.

Key Takeaways

  • No more paradigm tradeoffs: Relational integrity and document-model agility coexist on the same data, with zero duplication.
  • GraphQL-like syntax makes duality view definitions intuitive and maps directly to your application’s JSON shape.
  • Full DML cascading: A single JSON INSERT, UPDATE, or DELETE touches all underlying normalized tables with ACID guarantees.
  • Built-in ETags provide optimistic concurrency control out of the box – critical for modern concurrent, stateless backends.
  • REST and driver-ready: Duality views integrate natively with ORDS REST APIs and MongoDB-compatible drivers, enabling polyglot data access without custom middleware.

JSON Relational Duality Views are one of the most impactful features in Oracle Database 26ai. They let you model your data relationally while consuming it as documents – the architecture your applications deserve.

Scroll to Top