Oracle Corporation

05/02/2024 | Press release | Distributed by Public on 05/02/2024 08:05

JSON Relational Duality: The Revolutionary Unification of Document, ...

JSON Relational Duality is a landmark capability in Oracle Database 23ai (formerly Oracle Database 23c) that provides game-changing flexibility and simplicity for Oracle Database developers. This breakthrough innovation overcomes the historical challenges that developers have faced when building applications, either when using the relational model or when using the document model. For more information on the Oracle Database 23ai general availability and its key capabilities, please refer to the Oracle Database 23ai announcement blog.

JSON Relational Duality delivers a solution that provides the benefits of both relational tables and JSON documents, without the tradeoffs of either model.

Limitations using relational and document models for app dev

The relational approach is very powerful but not always the easiest for app dev

The relational model is very powerful and efficient since it uses data normalization to ensure data integrity while avoiding data duplication. Relational operations make modeling and accessing data very flexible, however, in some cases, it is not always the easiest for developers:

  • Developers typically build apps in terms of app-tier language objects, while relational databases store data as tables, rows, and columns. Constructing individual application-tier objects often requires accessing multiple tables.
  • To get around these difficulties, developers often use Object Relational Mapping (ORM) frameworks. While ORMs can simplify app-dev, they also introduce significant overheads: They usually require multiple database round-trips to manipulate a single app-tier object, they are inefficient because they do not take full advantage of the capabilities of the database engine, they do not manage concurrency control very well, and applications need to use different ORM frameworks for different languages. They are also extremely poor at batch or bulk operations that must insert or modify many app-tier objects.
  • Application-tier ORM frameworks also introduce the possibility of divergent semantics across modules and microservices unless all of them share exactly the same mapping information.

The relational model is therefore a very efficient data storage format but sometimes poses challenges for developers when used as a data access format and ORMs introduce inefficiencies and other trade-offs.

JSON document databases have their own shortcomings

Document databases are popular with developers because they make it easy to retrieve and store hierarchically organized data corresponding to app-tier language objects. The JSON document model allows apps to directly map objects into a hierarchical JSON format, avoiding the need for decomposition or reconstitution, and the associated complexities. However, the JSON document model is far from ideal as a storage format because:

  • Documents often need to store overlapping data. For example, different Order documents may store the same Customer information redundantly. Data duplication leads to inefficiency and potential inconsistency, since an update to shared information (such as a customer phone number) may require updating many Order documents atomically.
  • To get around this problem, some document databases recommend normalizing documents using references: Instead of including the Customer document within the Order document, an Order document may simply include an ID for that Customer document. However, normalizing documents completely defeats the simplicity of the document model, and results in a model that is actually the worst of both worlds!
  • It is also very difficult to model many-to-many relationships using the document model. Attempts to model the relationships lead to even greater data duplication and the potential for additional inconsistencies.

Documents/JSON are therefore a developer-friendly data access format and make it easy for developers to get started, but have significant limitations as a data storage format, especially as the complexity of an app increases.

How Oracle Database 23ai JSON Relational Duality revolutionizes app dev

Oracle Database 23ai JSON Relational Duality unifies the benefits of the Relational and Document worlds within a single database without any of the tradeoffs discussed earlier. The new feature in Oracle Database 23ai that enables this capability is referred to as a JSON Relational Duality View.

Figure 1: JSON Relational Duality: Best of both worlds

Using Duality Views, data is still stored in relational tables in a highly efficient normalized format but is accessed by apps in the form of JSON documents (figure 2). Developers can thus think in terms of JSON documents for data access while using the highly efficient relational model for data storage, without having to compromise simplicity or efficiency. In addition to this, Duality Views hide all the complexities of database level concurrency control from the user, providing document level serializability.

Figure 2: Stored as rows - Accessed as JSON documents

Duality Views can be declared over any number of tables using intuitive GraphQL syntax. For example, the following Duality View renders the relational data available in the order, orderitem and customer tables as a JSON document corresponding to an app-tier Order object:

Figure 3: Declaring a Duality View

Developers can easily define different Duality Views on the same or overlapping set of relational tables, making it easy to support multiple use cases on the same data (such as OrderObj and ShipmentObj Duality views that share common tables such as orderitem and customer).
Using Duality Views, developers now have much greater flexibility:

  • SQL access to all data, including data in JSON columns, using SQL JSON extensions
  • JSON document access to all data, including access to data stored in relational tables, using Duality Views

Developers can manipulate JSON documents produced by Duality Views in ways they are used to, using their usual drivers, frameworks, tools, and development methods.

Extreme simplicity and flexibility for developers

Developers greatly benefit from the simplicity of being able to retrieve and store all the data needed for a single app-tier object in a single database operation. Applications using Duality Views can now simply read a document from the view, make any changes they need, and write the document back without having to worry about the underlying relational structure. You can manipulate documents realized by Duality Views in the ways you're used to, using your usual drivers, frameworks, tools, and development methods:

  • Duality Views eliminate the need for ORM frameworks.
  • Document-centric applications can use document APIs, such as Oracle Database API for MongoDB, and Oracle REST Data Services (ORDS), or they can use SQL/JSON document functions.
  • Reads and writes of Duality Views can use familiar HTTP operations such as GET, PUT, and POST.
  • Applications that prefer an API over HTTP can use Oracle Database API for MongoDB.
  • Application operations using Duality Views are optimally executed inside the database since they enable fetching and storing all rows needed for an app-tier object use case in a single database access, in contrast with the often inefficient database access code generated by ORMs.

JSON Relational Duality therefore provides the storage, consistency and efficiency benefits of the relational model while also providing the simplicity and flexibility of the JSON document model.

Lock-Free Concurrency Control with Oracle Database 23ai

Duality Views also benefit from a novel lock-free or optimistic concurrency control architecture in Oracle Database 23ai that enables developers to manage their data consistently across stateless operations.

  • Traditional locking does not work with stateless operations such as REST GET and PUT since locks are stateful and cannot be held across stateless calls.
  • A new lock-free concurrency control algorithm in Oracle Database 23ai allows for consistent updates across stateless operations.
  • The lock-free scheme extends the Entity Tag (ETAG) concept from the HTTP protocol into the core database, an ETAG being a hash or a signature for the contents of a retrieved web page.
  • When a GET is performed on a Duality View, the returned JSON document also contains the ETAG of the set of rows used in constructing the document.
  • When that document is modified and later PUT back into the database, the supplied ETAG is compared with the current ETAG of the rows. If the ETAG differs, the object must have been modified between the GET and the PUT and the PUT is rejected
  • The application can then re-GET the document with the new ETAG and retry the PUT
  • If the PUT is successful, we are guaranteed that no intervening changes have occurred to the object and ensuring document-level atomicity and consistency.
  • Document-level serializability using lock-free concurrency control allows developers to focus on their app instead of implementing debugging concurrency control and data consistency issues within the application-tier.

In summary: JSON Relational Duality simplifies app dev

  • 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.
  • Duality Views are fully updatable JSON views over relational data. Applications can simply read a document corresponding to an app-tier object, make necessary changes, and write the document back without worrying about the underlying relational structure, type mapping, or data consistency.
  • Multiple Duality Views can be implemented across overlapping groups of tables, thus enabling flexible handling of multiple application use cases against the same data.
  • Application operations using Duality Views are optimally executed inside the database.
  • A new optimistic concurrency control algorithm allows high concurrency updates and interactive use cases, while providing powerful document level serializability.

Your next steps: Try JSON Relational Duality with Oracle Database 23ai for free

Developers can start building apps using Oracle Database 23ai features like JSON Relational Duality using one of the several available free options of Oracle Database 23ai. We are making it easy for you to experience the power and flexibility of Duality View in building apps. We are providing well-documented and easy-to-learn tutorials on GitHub. You can download the tutorials and use them with your choice of free Oracle Database 23ai. In the tutorials, developers can use SQL, REST and Oracle Database API for MongoDB to try features, capabilities and examples related to Duality Views.

Additional resources: