Overview of JSON-Relational Duality Views

1 Overview of JSON-Relational Duality Views

Duality views combine the advantages of using JSON documents with those of the relational model, while avoiding the limitations of each. JSON-relational duality underpins collections of documents with relational storage: active, updatable, hierarchical documents are based on a foundation of normalized relations.

A single JSON document can represent an application object directly, capturing the hierarchical relations among its components. A JSON document is standalone: self-contained and self-describing — no outside references, no need to consult an outside schema. There's no decomposition, which means that JSON is schema-flexible: you can easily add and remove fields, and change their type, as required by application changes.

However, relationships among documents are not represented by the documents themselves; the application must code relationships separately, as part of its logic. In particular, values that are part of one document cannot be shared by others. This leads to data duplication across different documents (whether of the same kind or different kinds), which in turn can introduce inconsistencies when documents are updated.
The relational model decomposes application objects ("business objects") into normalized tables, which are explicitly related but whose content is otherwise independent. This independence allows for flexible and efficient data combination (joining) that is rigorously correct and reliable.

This avoids inconsistencies and other problems with data duplication, but it burdens application developers with defining a mapping between their application objects and relational tables. Application changes can require schema changes to tables, which can hinder agile development. As a result, developers often prefer to work with document-centric applications.

A JSON-relational duality view exposes data stored in relational database tables as JSON documents. The documents are materialized — generated on demand, not stored as such. Duality views give your data both a conceptual and an operational duality: it's organized both relationally and hierarchically. You can base different duality views on data that's stored in one or more of the same tables, providing different JSON hierarchies over the same, shared data.

This means that applications can access (create, query, modify) the same data as a set of JSON documents or as a set of related tables and columns, and both approaches can be employed at the same time.

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^Foot 1 functions. You can manipulate documents realized by duality views in the ways you're used to, using your usual drivers, frameworks, tools, and development methods. In particular, applications can use any programming languages — JSON documents are the lingua franca.
Other applications, such as database analytics, reporting, and machine learning, can make use of the same data directly, relationally (as a set of table rows and columns), using languages such as SQL, PL/SQL, C, and JavaScript. You need not adapt an existing database feature or code that makes use of table data to instead use JSON documents.

A JSON-relational duality view directly defines and reflects the structure of JSON documents of a given kind (structure and field types). The view is based on underlying database tables, which it joins automatically to realize documents of that kind.

Columns of SQL data types other than JSON in an underlying table produce scalar JSON values in the documents supported by the view. Columns of the SQL data type JSON can produce JSON values of any kind (scalar, object, or array) in the documents, and the JSON data can be schemaless or JSON Schema-based (to enforce particular document shapes and field types). See Car-Racing Example, Tables for the column data types allowed in a table underlying a duality view.

JSON fields produced from an underlying table can be included in any JSON objects in a duality-view document. When you define the view you specify where to include them, and whether to do so individually or to nest them in their own object. By default, nested objects are used.^Foot 2

Note:

A given column in an underlying table can be used to support fields in different objects of a document. In that case, the same column value is used in each object: the data is shared.^Foot 3

A duality view can be read-only or completely or partially updatable, depending on how you define it. You can define a duality view and its updatability declaratively (what/where, not how), using SQL or a subset of the GraphQL language.

When you modify a duality view — to insert, delete, or update JSON documents, the relevant relational (table) data underlying the view is automatically updated accordingly.

We say that a duality view supports a set of JSON documents of a particular kind (structure and typing), to indicate both (1) that the documents are generated — not stored as such — and (2) that updates to the underlying table data are likewise automatically reflected in the documents.

Even though a set of documents (supported by the same or different duality views) might be interrelated because of shared data, an application can simply read a document, modify it, and write it back. The database detects the document changes and makes the necessary modifications to all underlying table rows. When any of those rows underlie other duality views, those other views and the documents they support automatically reflect the changes as well.

Conversely, if you modify data in tables that underlie one or more duality views then those changes are automatically and immediately reflected in the documents supported by those views.

The data is the same; there are just dual ways to view/access it.

Duality views give you both document advantages and relational advantages:

Document: Straightforward application development (programming-object mappings, get/put access, common interchange format)
Relational: Consistency, space efficiency, normalization (flexible data combination/composition/aggregation)

JSON-relational duality serves a spectrum of users and use cases, from table-centric, relational-database ones to document-centric, document-database ones.

Table-Centric Use Case for JSON-Relational Duality
Developers of table-centric database applications can use duality views to interface with, and leverage, applications that make use of JSON documents. Duality views map relational table data to documents.
Document-Centric Use Case for JSON-Relational Duality
Developers of document-centric applications can use duality views to interface with, and leverage, normalized relational data stored in tables.
Map JSON Documents, Not Programming Objects
A JSON-relational duality view declaratively defines a mapping between JSON documents and relational data. That's better than mapping programming objects to relational data.
Duality-View Security: Simple, Centralized, Use-Case-Specific
Duality views give you better data security. You can control access and operations at any level.
Oracle Database: Converged, Multitenant, Backed By SQL
If you use JSON-relational duality views then your application can take advantage of the benefits of a converged database.