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.
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
|
|---|
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
Post a Comment