Build AI-Friendly Applications with Oracle JSON-Relational Duality Views

Modern enterprise applications are increasingly expected to support more than traditional CRUD operations. They need to expose APIs, serve modern web and mobile applications, integrate with AI assistants, support RAG workflows, and provide data in formats that application frameworks can consume easily.

At the same time, many enterprise systems already rely on well-designed relational databases.

This creates an important architectural question:

How can we expose an existing relational application as hierarchical JSON for modern applications and AI systems without abandoning the relational database model?

Oracle JSON-Relational Duality Views provide an approach to this problem by allowing the same business data to be accessed from both a relational and document-oriented perspective.

The goal is not to replace relational databases with JSON documents.

Instead, the goal is to keep the relational database as the system of record while exposing application-friendly JSON representations when they are needed.

1. The Problem: Relational Applications vs. AI Applications

Enterprise applications commonly store business data in normalized relational tables because this structure is well suited for maintaining relationships, enforcing constraints, processing transactions, generating reports, and performing SQL-based data access. For example, a customer-order system may contain related tables such as CUSTOMERS, ORDERS, ORDER_ITEMS, and PRODUCTS, where one customer can have multiple orders and each order can contain multiple items. However, modern applications increasingly consume data in hierarchical JSON format. An application or AI agent may prefer to receive a complete customer document containing the customer details, orders, and order items in a single JSON structure. This format can make it easier to retrieve related information, provide context to an LLM, transform data for application use, or expose business data through APIs. The challenge, therefore, is not choosing between relational data and JSON, but providing document-oriented access to existing relational data without maintaining a separate copy of the same business information. JSON-Relational Duality Views address this challenge by providing a JSON document interface over relational tables while keeping the underlying data relational and consistent.

2. What Are JSON-Relational Duality Views?

Oracle JSON-Relational Duality Views provide two perspectives of the same underlying business data: a traditional relational perspective for SQL and PL/SQL, and a hierarchical JSON perspective for REST APIs, frontend applications, and AI workloads. The key concept is duality—the business data remains stored in relational tables, while applications can access that same information as JSON documents. A Duality View creates a mapping between relational table data and JSON documents, rather than requiring developers to maintain a separate JSON copy of the data. This approach is particularly useful when an existing database contains normalized relational data but modern applications require hierarchical JSON, APIs need document-oriented responses, or AI agents need structured business context. The core architectural principle is therefore simple: keep the relational database as the source of truth and expose a JSON representation whenever the consuming application requires a document-oriented format.

3. Start with a Real Existing Relational Application

Instead of creating a completely new JSON-based application, consider an existing Customer Order Management System that is already running successfully on a relational database. The system contains related tables such as CUSTOMERS, ORDERS, ORDER_ITEMS, and PRODUCTS, with established relationships between customers, their orders, the items within those orders, and the products being purchased. The existing application already depends on this relational model, so redesigning or replacing the database simply because a new AI assistant, REST API, or modern frontend requires JSON would introduce unnecessary complexity and risk. A more practical approach is to modernize the existing application by adding JSON-Relational Duality Views on top of the current relational tables. The architecture can therefore evolve from the existing relational application to its existing tables, then to Duality Views, and finally to modern JSON, REST, and AI applications. This allows the relational schema to remain the foundation and source of truth while providing modern applications with the document-oriented JSON representation they require. This makes JSON-Relational Duality Views particularly valuable for incremental modernization of existing enterprise applications rather than only for greenfield development.

4. The Relational Schema

The foundation of the example is a normalized relational schema consisting of CUSTOMERS, ORDERS, ORDER_ITEMS, and PRODUCTS. The CUSTOMERS table stores customer information, while ORDERS maintains the orders placed by each customer. Each order can contain multiple records in ORDER_ITEMS, and each order item references a product stored in the PRODUCTS table. This structure keeps business data organized, maintains referential relationships, and provides a reliable foundation for transactional applications. For example, the customer table can be created as follows:


The related tables can be defined using foreign keys:


5. Create Your First JSON-Relational Duality View

This is where the architecture moves beyond manually constructing JSON using functions such as JSON_OBJECT() and JSON_ARRAYAGG(). Instead, Oracle JSON-Relational Duality Views can be used to define a document-oriented representation of relational data. A simple example is a customer view that maps columns from the CUSTOMERS table to fields in a JSON document:


Here, the JSON fields are mapped directly to columns in the relational table. For example, a row in the CUSTOMERS table can be represented as the following JSON document:


The important concept is that the application receives a document-oriented representation, while the underlying business data continues to be managed through the relational database. Developers can therefore expose customer information in JSON without creating a separate customer JSON table or maintaining duplicate business data. This provides a natural bridge between traditional relational applications and modern JSON-based applications, APIs, and AI workloads.

6. Build a Nested Customer → Orders → Items Document

The real value of JSON-Relational Duality Views becomes more apparent when multiple related tables need to be represented as a single document. In our Customer Order Management System, the relational database stores customer, order, order-item, and product information across separate tables connected through primary-key and foreign-key relationships. However, a modern application or AI agent may need this information as one hierarchical JSON document. For example, a customer document can contain the customer details, an array of orders, and within each order, an array of order items with their corresponding product information:


Conceptually, the data flows from CUSTOMERS → ORDERS → ORDER_ITEMS → PRODUCTS, while the consuming application receives the result as a single document hierarchy. This is the key advantage of the duality approach: the application can work with a document-oriented structure while the database continues to maintain the underlying relational relationships and constraints. In a production implementation, the Duality View should be designed according to the application's actual primary-key and foreign-key relationships, document structure, and required update semantics. This allows the JSON representation to reflect the existing business model without requiring developers to redesign the relational schema.

7. One Relational Model, Multiple JSON Representations

One of the most useful architectural capabilities of JSON-Relational Duality Views is that the same underlying relational data can be exposed through multiple JSON representations, depending on the needs of different applications. For example, the existing relational tables can support both a Customer Duality View and an Order Duality View. A customer-facing application may require a customer-centric document containing basic customer information and associated orders:


At the same time, an order-management application may require an order-centric document containing the order status and its items:






Both documents can be derived from the same underlying relational business data without creating separate copies of the information. This allows each application to consume a JSON structure appropriate to its specific requirements. For example, customer applications can use customer-centric documents, order-management systems can use order-centric documents, mobile applications can receive smaller payloads, and AI agents can consume richer documents containing the context required for a particular task. The key architectural principle is that the relational model remains shared as the source of truth, while JSON-Relational Duality Views allow the application-facing representation to be specialized for different use cases.

8. Use the Duality View as a REST API

A modern application often communicates with backend systems through REST APIs rather than accessing database tables directly. JSON-Relational Duality Views can fit naturally into this architecture by providing a JSON document interface between the REST layer and the underlying relational database. With Oracle REST Data Services (ORDS), a Duality View can be exposed through a REST endpoint so that applications can retrieve business data using standard HTTP requests. Conceptually, a client could make a request such as:

GET /ords/app/customer_orders_dv/1

The API could return a hierarchical customer document:


9. Oracle JSON Duality Views vs. MySQL REST Service

The MySQL portion of this architecture is useful as a technology comparison, but it should not be presented as an implementation of Oracle JSON-Relational Duality Views because the two technologies solve related but different problems. In the Oracle approach, existing relational tables can be mapped into document-oriented JSON through a JSON-Relational Duality View, which can then be exposed through Oracle REST Data Services (ORDS) as a REST API. The architecture can be represented as Oracle Relational Tables → JSON-Relational Duality View → ORDS → JSON REST API. In comparison, the MySQL approach can provide REST-based access to MySQL data through a MySQL REST Service, creating an architecture such as MySQL Data → MySQL REST Service → HTTPS REST API → Application. MySQL REST Service provides capabilities for accessing MySQL data through HTTPS-based REST interfaces, including RESTful web services, REST SQL, data mapping, and client SDK generation. Therefore, the two approaches can be compared at the architecture and API-access level, but MySQL REST Service should not be described as providing Oracle's JSON-Relational Duality View functionality. The distinction is important because the underlying technologies, data-to-JSON mapping mechanisms, and implementation models are different.

10. Connect the Duality View to an AI Assistant

The JSON representation can also serve as a controlled data interface for an AI assistant. Consider a user asking, “What did customer 1 order?” Instead of giving the LLM direct access to database tables, the AI application can call a controlled REST API that retrieves only the relevant customer document through the JSON-Relational Duality View. The architecture can therefore follow the flow User → AI Assistant → AI Agent → REST API → JSON-Relational Duality View → Oracle Database. For example, the API may return a structured document containing the customer's information and order details:


11. Build a RAG Architecture

The same architecture can be extended into a Retrieval-Augmented Generation (RAG) workflow, where the AI application retrieves relevant business information before generating a response. The relational database remains the source of truth, while the JSON-Relational Duality View provides a structured document representation that can be consumed by the AI layer. A typical workflow can be represented as Relational Tables → JSON-Relational Duality View → REST API → Document Normalization → Embedding Model → Vector Store → Semantic Search → Relevant JSON → LLM → Answer. Instead of sending the entire database to the LLM, the application first identifies and retrieves the information relevant to the user's question.

12. Add an AI Agent

An LLM that only generates text is different from an AI agent that can determine which tools or APIs it needs to call to complete a task. The architecture can therefore be extended by placing an AI agent between the user and the application's controlled data-access APIs. For example, the agent can have access to separate Customer and Order tools, with each tool communicating with an appropriate JSON-Relational Duality View. The overall flow becomes User → AI Agent → Customer Tool / Order Tool → Duality View → Oracle Database

13. When Should Developers Use JSON-Relational Duality Views?

JSON-Relational Duality Views are particularly useful when an application already has a relational data model but needs document-oriented access for modern applications, APIs, or AI workloads. They are especially relevant when developers want to modernize an existing system without duplicating business data or replacing the relational database. Consider using them in the following scenarios:

  1. You already have a normalized relational application
    An established database can continue to store the business data without introducing a separate document database simply to support JSON-based consumers.
  2. Your API requires hierarchical JSON
    If an application naturally works with nested structures such as customers containing orders and orders containing items, a Duality View can provide a document-oriented representation.
  3. Multiple applications need different representations
    Different consumers may require different document structures while continuing to work with the same underlying relational data.
  4. You need both relational integrity and JSON access
    The relational database can remain the foundation for relationships, constraints, and transactions while applications consume data through JSON documents.
  5. You are modernizing an existing application
    Duality Views can act as a modernization layer, allowing REST APIs, mobile applications, and other modern consumers to be introduced without completely redesigning the existing relational schema.
  6. You are building AI or RAG applications
    AI agents and RAG workflows can use structured JSON documents as application context instead of requiring direct access to raw relational tables.
  7. You want document-oriented API boundaries
    A Duality View can provide a natural document boundary for REST-based architectures, allowing application developers to work with business entities as JSON documents.

Overall, JSON-Relational Duality Views are most valuable when you want to preserve the strengths of an existing relational database while providing modern document-oriented access. They are particularly well suited to incremental modernization, API development, and AI-enabled applications where the same enterprise data needs to be consumed in different ways.

14. When Should You Not Use JSON-Relational Duality Views?

JSON-Relational Duality Views should not be introduced simply because an application uses JSON. They are an architectural option for providing document-oriented access to relational data, not a replacement for every SQL query, conventional database view, or SQL/JSON feature. A simpler approach may be more appropriate when the application is primarily analytical and SQL already meets its requirements, when only a one-time JSON response is needed, or when a straightforward SQL/JSON query can produce the required output. They may also be unnecessary when the application does not have a genuine document-oriented access requirement, when the required JSON structure changes extremely frequently, or when the relational model does not naturally map to the desired document structure.

For example, if an application only needs to return two customer attributes as JSON, a simple SQL/JSON query may be sufficient:


15. Trade-offs: Choosing the Right Approach

Approach

Best For

Main Strength

Key Trade-off

Relational Tables

Transactional applications

Strong integrity, relationships, SQL

Application may need object/JSON mapping

SQL/JSON Functions

Individual JSON responses

Flexible query-level JSON generation

Complex JSON can become repetitive

JSON Columns

Flexible document storage

Schema flexibility

Requires careful indexing and validation

JSON-Relational Duality Views

Relational data + document access

Same relational data exposed as JSON

Requires suitable schema and supported features

Custom REST API

Complex application-specific APIs

Maximum API/business-logic control

More code to develop and maintain

MySQL REST Service

REST access to MySQL data

Convenient REST-based access

Different architecture from Oracle Duality Views


Conclusion

Building AI-friendly applications does not require abandoning the relational database. In many enterprise systems, the relational model already provides a strong foundation through structured data, relationships, constraints, transactions, and mature SQL-based access. The challenge is to make this existing business data easier for modern applications to consume. Oracle JSON-Relational Duality Views provide a bridge between relational data and document-oriented application experiences, allowing the same business information to support JSON documents, REST APIs, web applications, AI assistants, and agentic workflows.

The central architectural principle is simple: keep the relational database as the system of record and expose document-oriented JSON representations to applications that need them. This is particularly valuable when modernizing existing enterprise applications because developers can introduce a JSON-oriented access layer without replacing the existing relational schema. ORDS can expose these representations through REST APIs, while AI agents and RAG workflows can consume the resulting JSON as structured business context.

At the same time, Duality Views should not be treated as a universal replacement for SQL, traditional database views, SQL/JSON functions, JSON columns, or custom APIs. The appropriate approach depends on the application's data model, workload, API requirements, document structure, and access patterns.

Comments

Popular posts from this blog

Implementing Authentication and Authorization in Oracle APEX

Mastering Shared Components in Oracle APEX

Designing a Scalable Database Architecture for Oracle APEX Applications