Oracle Announces General Availability of JSON Relational Duality in Oracle Database 23ai

May 2, 2024 | 8 minute read
Ranjan Priyadarshi
Senior Director, Product Management(Mission-Critical Data and AI Engines)
Text Size 100%:

 

JSON Relational Duality is a new capability in Oracle Database 23ai that unifies the benefits of both the relational and document models.

Why is this such an important feature? It is because, till now, developers have had to use either the relational model or the JSON document model, to both store and access application data. Therefore, they had to make a choice that involved acquiring the benefits as well as the drawbacks of the model they selected:

  • The relational data model is ideal as a storage format due to the power of normalization, which eliminates data duplication and provides consistency and efficiency. However, the relational model is not as ideal as an access format for building applications since applications process data in terms of hierarchical objects. To address this issue, developers have used complex and inefficient ORMs (Object-Relational Mapping) to map application objects to relational data.

  • The document model, such as JSON, is ideal as an access format since it is hierarchical and self-contained, making it easy to map to application objects. However, JSON documents are poor as a storage format since different documents can repeat the same data, resulting in data duplication, updates to multiple copies, and, therefore, potential inconsistencies—the very problem that the relational model solves via normalization.

JSON Relational Duality is a breakthrough innovation that overcomes the historical challenges developers have faced when choosing between a relational model or a document model for building applications. Developers can now get the flexibility and data access simplicity of the document model as well as the efficiency, consistency, and use case flexibility of the relational model on the same underlying data.

Introduction

JSON Relational Duality bridges the gap by providing a unified solution that uses the strengths of both relational and document models. It decouples the access and storage formats, allowing data to be stored as relational but accessed as JSON. The new feature, JSON Relational Duality in 23ai, allows data to remain in normalized relational tables but be accessed as JSON documents. This duality of access and storage eliminates the need to choose between relational and document models for application development. 

Figure 1. JSON Relational Duality provides a unified solution.
Figure 1. JSON Relational Duality provides a unified solution

JSON Relational Duality View (or simply, “Duality View”) can be defined using intuitive GraphQL-like syntax. The goal of the Duality View shown below is to generate a JSON document corresponding to each Order. The view specifies the intended shape of the JSON document, the tables from which to obtain the data, and the columns from which to obtain the individual fields of the document:

Figure 2. Example defining JSON Relational Duality Views
Figure 2. Example defining JSON Relational Duality Views

Duality Views can be defined using SQL with standard JSON extensions, but GraphQL provides a simpler and more intuitive shorthand syntax. For example, GraphQL automatically adds the correlation conditions based on Primary Key / Foreign Key relationships, such as the ones based on the customer id (cid) and order id (oid) columns, that are necessary to generate the JSON document for the view.

A Duality View can be read-only or completely or partially updatable, depending on how you define it. Developers can modify JSON documents generated by Duality Views in the ways they are used to, using their favorite access method, drivers, and frameworks. Developers can operate on the same data as relational using standard SQL or documents using standard document APIs such as REST, Simple Oracle Document Access (SODA), and MongoDB API, or they can use SQL/JSON.

A JSON Relational Duality View is essentially a fully updatable and transactional JSON view over data. When documents in a Duality View are updated or deleted, the relevant relational (table) data underlying the view is automatically updated. Apps can simply read a document, make any necessary changes, and write the document back without worrying about underlying data structure, mapping, consistency, or performance tuning. Using Duality Views, several hierarchies can be defined on the same underlying relational data based on different use cases in your application.

Benefits of using JSON Relational Duality

Best of both worlds of Relational and JSON

Using JSON Relational Duality Views, data is stored in relational format with the full benefits of data normalization, but data can be accessed as JSON documents to make it easy for applications to access the database. JSON Relational Duality unifies the benefits of the relational and document worlds on the same data in a single database. Developers no longer have to choose - they get the data consistency and multi-use case flexibility of relational databases, the flexibility of JSON, and even a MongoDB-compatible document store API.

Figure 3. JSON Relational Duality unifies the benefits of both worlds
Figure 3. JSON Relational Duality unifies the benefits of both worlds

 

Architectural superiority to Object Relational Mapping (ORM)

For a developer, the relational model often requires creating a bridge between object-oriented programs and relational databases using an ORM layer. This extra layer burdens the developer with custom mappings between application objects and tables – adding extra complexity, inefficiency, and performance penalties for multiple roundtrips to the database.

  • ORMs typically issue multiple SQL statements to read and write app objects, degrading performance. Duality Views, which can be read and written in a single database round trip, are far more efficient.
  • ORMs are programming language specific, and one ORM implementation cannot be used by multi-language applications, e.g., in which some microservices are coded in Java and some microservices are coded in Python.

Duality Views eliminate the need for ORM frameworks. They are database-resident, which gives them a significant advantage over ORMs. When a Duality View is used, only one round trip is needed to read and write documents. Mapping app objects to relational tables becomes much simpler and efficient. Because a Duality View is centrally located in the database and can be used by multiple apps, it also eliminates data inconsistencies across apps.

Architectural superiority to Document databases

Document databases may appear easier for developers to start with since a given document can contain all the data needed for a specific application use case. However, they progressively become difficult to manage consistently since different documents, even within the same collection, often repeat the same data.

By normalizing the data into multiple tables, JSON Relational Duality enforces data integrity and eliminates data duplication while storing the data. Underlying data is always transactionally consistent, whether updated through SQL APIs or Document APIs. This consistency across apps is critical for complex enterprise applications.

Ultra-Flexible Data Access: Depending on the use case, you can access or update data using SQL, REST, or Document APIs (which are MongoDB compatible)

JSON Relational Duality gives the ability to issue SQL against both documents and relational, plus the ability to use Document APIs against both documents and relational. Freely use Standard SQL, SQL with JSON extensions, or Document APIs, depending on the use case.

Once a Duality View is created on underlying relational tables, it can be accessed transparently as a JSON document through a variety of Document APIs: REST, a MongoDB-compatible API, Oracle Simple Object Data API (SODA), as well as ANSI SQL with extensions for JSON (such as JSON_VALUE()). 

In addition, the underlying tables can continue to be accessed directly using conventional SQL without any loss of consistency since there is only one copy of stored data. Using direct SQL is often more useful than JSON for bulk operations, analytics, and reporting.

Ultra-Simple Application Evolution with support for schema-flexible documents

Duality Views make it extremely easy to evolve applications as they add new use cases without creating new copies of data. As apps add more and more use cases, each new use case can be handled with a new Duality View against the same underlying data without any data duplication. Duality Views are logical views against a common set of underlying tables. Even if the same data is accessed using different views, there is no physical data duplication and, hence, no data inconsistencies.

Easily build REST-ful applications and caches

Traditional database locking does not work well with REST applications since REST is a stateless protocol, and transaction locks cannot be held across REST calls. Likewise, application-tier database caches and mobile applications cannot hold locks to protect the validity of cached data. Duality Views utilize a Lock-free or Optimistic concurrency control mechanism that allows fully consistent database reads and writes without needing locks across accesses.

Centralize security rules for apps using Duality Views

Duality Views also enable the enforcement of many security rules to be centralized, verified, and audited in the database instead of forcing developers to enforce them in every application.

Fine-grained customization of data access and actions

Duality Views allow simple and fine-grained field-level customization of data access and modification rules for application modules sharing the same data. Duality Views support updatability annotations that can be used to restrict operations on a per-view basis.

Migrate your apps from Document databases to Oracle Database

Now that you know all the benefits of JSON Relational Duality, you can easily migrate your Apps from Document databases to Oracle or build new apps using JSON Relational Duality on Oracle.

An intelligent migration assistant, the JSON-to-Duality Migrator will help in your journey of migrating apps from Document databases to Oracle. This new feature automatically recommends and creates a normalized relational schema and a set of Duality Views based on your JSON collections. It automatically identifies the normalized relational tables that should be created, along with primary, unique, and foreign key constraints, indexes, and default values. In addition, the feature imports data from the JSON collections into the resulting Duality Views.

Using JSON-to-Duality Migrator, you can quickly migrate apps from Document databases to Duality Views with a few simple steps and take advantage of all the benefits of JSON Relational Duality and the Oracle Converged Database.

Summary: Get started with JSON Relational Duality using 23ai

JSON Relational Duality bridges the document-relational mismatch by providing a unified solution that brings together the strengths of both relational and document models. This eliminates the need to choose between relational and document models for application development. Developers now get the flexibility and data access benefits of the JSON document model, as well as the storage efficiency and power of the relational model. JSON Relational Duality will greatly simplify application development and increase developer productivity. Get started with JSON Relational Duality using Oracle Database 23ai and 23ai Free.


Additional resources:

Ranjan Priyadarshi

Senior Director, Product Management(Mission-Critical Data and AI Engines)

At Oracle, Ranjan is the Product Management Leader for the Mission-Critical Data and AI Engines team, where he manages several Oracle Database features, including AI Vector Search, JSON Relational Duality, Database In-Memory, and Advanced Compression. In addition, Ranjan supports Oracle TimesTen and Oracle NoSQL. 

Twitter: https://twitter.com/ranjanpriya (@ranjanpriya)
LinkedIn: https://www.linkedin.com/in/ranjan-priyadarshi/
Email: ranjan.priyadarshi@oracle.com