JSON Duality Views: Advanced Query Patterns

After a year working with JSON Relational Duality Views, the patterns that make them genuinely powerful in production have become clear. Here are the advanced techniques.

Filtering on nested JSON fields:

-- Find orders where any item's quantity exceeds 10
SELECT data
FROM   order_dv
WHERE  JSON_EXISTS(data, '$.items[*]?(@.qty > 10)');

Combining duality view queries with relational joins:

Duality views can be joined with regular tables in SQL:

SELECT o.data.customer, o.data.status, c.credit_limit
FROM   order_dv o
JOIN   customers c ON c.customer_id = o.data._id.customer_id
WHERE  o.data.status = 'PENDING'
AND    c.credit_limit < 1000;

Using ORDS to expose duality views as REST APIs:

Once a duality view is published via ORDS, you get:

• GET /api/orders — paginated list of all orders as JSON documents
• GET /api/orders/42 — single order document
• PUT /api/orders/42 — full document replacement (with optimistic locking via ETag)
• POST /api/orders — insert a new order (decomposed into relational tables)

No controller code required. Oracle handles the JSON ↔ relational mapping.

Optimistic locking with ETags:

-- Each duality view row includes a system-generated ETag
SELECT data, ETAG
FROM   order_dv
WHERE  data._id = 42;

-- Update only succeeds if the ETag matches (no concurrent modification)
UPDATE order_dv
SET    data = JSON_MERGEPATCH(data, '{"status": "SHIPPED"}')
WHERE  data._id = 42
AND    ETAG = :last_known_etag;

This implements optimistic concurrency control — concurrent modifications are rejected rather than silently overwriting each other. A valuable pattern for mobile and REST APIs.