# What Are JSON Duality Views in Oracle 26ai and How Do They Simplify AI Development?

> Discover JSON Duality Views in Oracle 26ai. Access relational data as JSON for AI apps without duplication, ensuring ACID compliance and simplifying LLM integration.

- Repository: [Oracle Developers/oracle-ai-developer-hub](https://github.com/oracle-devrel/oracle-ai-developer-hub)
- Tags: deep-dive
- Published: 2026-05-10

---

**JSON Duality Views in Oracle 26ai expose relational tables as native JSON documents without data duplication, enabling AI applications to directly consume LLM-generated payloads while maintaining full ACID compliance and relational integrity.**

The `oracle-devrel/oracle-ai-developer-hub` repository demonstrates how this feature bridges the gap between document-oriented AI workflows and enterprise relational databases. By implementing the FitTrack sample application, the repository shows how developers can eliminate complex ORM mapping layers and ETL pipelines when handling generative AI output.

## Understanding JSON Duality Views in Oracle 26ai

### The Dual Nature: Relational Storage with JSON Access

JSON Duality Views provide a **dual abstraction layer** over standard relational tables. The underlying data remains in conventional tables—complete with constraints, indexes, foreign keys, and transaction semantics—while the view presents each row as a single JSON object. According to the FitTracker implementation in [`apps/FitTracker/repositories/002_duality_views.sql`](https://github.com/oracle-devrel/oracle-ai-developer-hub/blob/main/apps/FitTracker/repositories/002_duality_views.sql), a view named `users_dv` projects the `users` table into JSON format:

```sql
CREATE OR REPLACE VIEW users_dv AS
SELECT JSON_OBJECT(
         'id' VALUE id,
         'email' VALUE email,
         'passwordHash' VALUE password_hash,
         'role' VALUE role,
         'status' VALUE status
       ) AS data
FROM users;

```

This architecture means the database maintains one physical source of truth. The relational schema enforces data integrity rules, while the duality view offers a document-style API that modern AI frameworks expect.

### Automatic Synchronization Without ETL

Because the view is merely a projection of the base table, modifications propagate bidirectionally without additional infrastructure. Inserts, updates, or deletes performed against `users_dv` immediately reflect in the `users` table, and changes to the base tables appear instantly in the view. This eliminates the need for change-data-capture pipelines, webhook handlers, or scheduled synchronization jobs that typically add latency and failure points to AI applications.

## How JSON Duality Views Simplify AI Application Development

### Native Support for LLM-Generated JSON Payloads

Generative AI models typically return structured JSON output. Without duality views, developers must parse this JSON and map it to relational columns manually or through an ORM layer. With Oracle 26ai, the AI-generated payload inserts directly into the view:

```python
cursor.execute(
    """
    INSERT INTO users_dv (data) VALUES (:1)
    """,
    [json.dumps({
        "_id": user_id,
        "email": email,
        "passwordHash": password_hash,
        "role": "user",
        "status": "pending"
    })]
)

```

As shown in [`apps/FitTracker/README.md`](https://github.com/oracle-devrel/oracle-ai-developer-hub/blob/main/apps/FitTracker/README.md), this pattern allows raw LLM output to persist immediately while the database handles validation against the underlying relational schema.

### Eliminating Document-Relational Synchronization Overhead

Traditional AI architectures require separate document stores (for flexibility) and relational databases (for reporting and integrity). Synchronizing these systems introduces network hops, eventual consistency delays, and complex error-handling code. JSON Duality Views consolidate these into a single tier—the Oracle 26ai database engine manages both representations simultaneously, reducing application complexity and improving read-after-write consistency for AI-driven transactions.

### Maintaining ACID Compliance and Security

Unlike document databases that trade consistency for flexibility, duality views inherit all Oracle Database security features. Row-level security, role-based access controls, and audit trails remain active when accessing data through the view. The view does not bypass security controls; it merely offers an alternate representation. This ensures that AI applications handling sensitive data meet compliance requirements without additional engineering effort.

## Implementing JSON Duality Views: Code Examples from FitTrack

### Creating a Duality View in SQL

The migration file at [`apps/FitTracker/repositories/002_duality_views.sql`](https://github.com/oracle-devrel/oracle-ai-developer-hub/blob/main/apps/FitTracker/repositories/002_duality_views.sql) defines the schema transformation. Each relational column maps to a JSON key using standard SQL/JSON functions, creating the document abstraction layer.

### Inserting AI-Generated JSON via Python

The repository's implementation in [`src/fittrack/repositories/base.py`](https://github.com/oracle-devrel/oracle-ai-developer-hub/blob/main/src/fittrack/repositories/base.py) provides generic CRUD operations that leverage the view. For user creation, the code passes the AI-generated dictionary directly to the database:

```python
@router.post("/users")
async def create_user(payload: dict, db: Session = Depends(get_db)):
    # payload comes from LLM, already JSON‑serialisable

    stmt = text("INSERT INTO users_dv (data) VALUES (:json)")
    db.execute(stmt, {"json": json.dumps(payload)})
    db.commit()
    return {"message": "User created"}

```

This excerpt from [`src/fittrack/api/routes/users.py`](https://github.com/oracle-devrel/oracle-ai-developer-hub/blob/main/src/fittrack/api/routes/users.py) demonstrates how FastAPI routes can accept raw JSON bodies and persist them through the duality view without intermediate transformation logic.

### Querying and Updating JSON Documents

Developers can query the document view using Oracle's JSON functions while the optimizer translates these to efficient relational operations:

```sql
SELECT data
FROM users_dv
WHERE JSON_VALUE(data, '$.email') = 'alice@example.com';

```

Updates merge changes into the underlying row using `JSON_MERGE_PATCH`:

```sql
UPDATE users_dv
SET data = JSON_MERGE_PATCH(data, '{"status":"active"}')
WHERE JSON_VALUE(data, '$._id') = :user_id;

```

The database applies these updates to the base table, preserving all foreign key constraints and trigger logic.

## Key Files and Implementation Patterns

The `oracle-devrel/oracle-ai-developer-hub` repository contains several critical reference implementations:

- **[`apps/FitTracker/README.md`](https://github.com/oracle-devrel/oracle-ai-developer-hub/blob/main/apps/FitTracker/README.md)** — Documents the conceptual architecture and provides the Python insertion example.
- **[`apps/FitTracker/repositories/002_duality_views.sql`](https://github.com/oracle-devrel/oracle-ai-developer-hub/blob/main/apps/FitTracker/repositories/002_duality_views.sql)** — Contains the DDL statements that create the `*_dv` view definitions.
- **[`src/fittrack/repositories/base.py`](https://github.com/oracle-devrel/oracle-ai-developer-hub/blob/main/src/fittrack/repositories/base.py)** — Implements generic data access patterns that work with both traditional SQL and duality view queries.
- **[`src/fittrack/api/routes/users.py`](https://github.com/oracle-devrel/oracle-ai-developer-hub/blob/main/src/fittrack/api/routes/users.py)** — Shows production-grade FastAPI routes that handle AI-generated JSON payloads.
- **[`src/fittrack/core/database.py`](https://github.com/oracle-devrel/oracle-ai-developer-hub/blob/main/src/fittrack/core/database.py)** — Manages the Oracle 26ai connection pool configuration used throughout the application.

## Summary

- **JSON Duality Views in Oracle 26ai** project relational tables as JSON documents without copying data or sacrificing ACID guarantees.
- **AI applications benefit** by directly inserting LLM-generated JSON into the database, eliminating ORM mapping complexity and synchronization latency.
- **Automatic bidirectional sync** ensures that document-style inserts and relational queries always see consistent data.
- **Enterprise security features** remain active on the view layer, maintaining compliance while supporting modern document APIs.

## Frequently Asked Questions

### What is the difference between a JSON Duality View and a regular JSON column?

A standard JSON column stores document data independently of relational structure, requiring application-level validation and foreign key management. A JSON Duality View, as implemented in Oracle 26ai, is a live projection of existing relational rows—updates to the view modify the underlying tables, and constraints are enforced automatically by the database engine.

### Do JSON Duality Views support complex nested documents?

Yes. The `JSON_OBJECT` construction in the view definition can nest subqueries and aggregate relational data into hierarchical JSON structures. The FitTrack repository demonstrates basic flat mapping, but Oracle's SQL/JSON generation functions support arbitrary nesting depth while maintaining relational normalization underneath.

### How do updates through a duality view affect relational constraints?

Updates propagate immediately to the base tables and undergo the same constraint validation as standard SQL `UPDATE` statements. If a JSON merge would violate a `NOT NULL` constraint, foreign key rule, or check constraint, the database raises an error and rolls back the transaction, preserving data integrity.

### Is Oracle 26ai required to use JSON Duality Views?

The feature is available in Oracle Database 23c and later versions, including the Oracle 26ai Free distribution used in the `oracle-ai-developer-hub` repository. The managed Oracle 26ai service additionally provides Docker and OCI deployment options with automated scaling and backup management.