JSON Relational Duality Views were introduced in April. Having had more time to work with them, let’s talk about what works well in production and where to be careful.
What works well:
Simple document APIs: If your documents map to a small number of normalized tables (2-4 tables), duality views are excellent. The REST API you get via ORDS is clean, consistent, and requires almost no backend code.
Write consistency: Because duality views write through to relational tables in a single transaction, you get ACID guarantees that no external MongoDB + Oracle sync can provide. Write to the duality view; both your JSON API and your SQL reports see the same data, immediately.
-- ORDS exposes this as a REST endpoint automatically
-- GET /api/orders/42 → returns the JSON document
-- PUT /api/orders/42 → updates through to relational tablesWhere to be careful:
Complex joins: Duality views over many tables with complex join conditions become difficult to reason about. If your document model doesn’t align reasonably with your relational model, the duality view definition becomes a maintenance burden.
Aggregations: Duality views represent individual documents (rows/graphs), not aggregations. You cannot expose aggregated data (counts, sums) as a duality view — that’s still a job for regular views or materialized views.
Caching behavior: Applications that cache JSON responses aggressively may see stale data if they don’t respect Oracle’s ETags that duality views generate for optimistic locking.
Design rule of thumb: Use duality views when your document model has a clear primary entity (Order, Customer, Product) that owns related data. Avoid them for cross-cutting concerns or heavily aggregated responses.
