Overview of JSON-Relational Duality Views

1 Overview of JSON-Relational Duality Views

JSON-relational duality gives you two points of view on the same data: a document-centric view, as a set of JSON documents, and a table-centric view, as a set of relational tables. It combines the advantages of each point of view, while avoiding their respective limitations.

A JSON-relational duality view exposes data stored in underlying database tables as collections of JSON documents; it is a mapping between table data and documents.

Without duality views, document collections and relational tables are quite different:

Document Collections:

Advantages: You can represent application objects directly, capturing hierarchical relations among their components. Documents are self-contained and schema-flexible.
Disadvantages: Applications need to define and handle relations among documents. In particular, they may need to provide code to share values across documents instead of duplicating them.

Relational Tables:

Advantages: Tables are independent, except for their explicitly declared relations. This allows flexible, efficient combination and avoids duplication.
Disdvantages: Developers need to map table data to application objects. Application changes can require table redefinition, which can hinder agile development.

Using duality views, applications can access (create, query, modify) the same data as either (1) a collection of JSON documents or (2) a set of related tables and columns. Both approaches can be employed at the same time by different applications or the same application. JSON-relational duality in fact serves a spectrum of users and use cases, from entirely table-centric, relational-database ones to entirely document-centric, document-database ones.

Duality-view data is stored relationally, and it can be accessed directly using the underlying tables. But the same stored data can also be read and updated as documents: when read, a document is automatically assembled from the table data; when written, its parts are automatically disassembled and stored in the relevant tables. You declaratively define a duality view, and the correspondence between table data and documents is then handled automatically by the database.

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.

Let's look at a simple example. We define a department table and a department duality view over just that table.

CREATE TABLE dept_tab
  (deptno     NUMBER(2,0),   
   dname      VARCHAR2(14),   
   code       NUMBER(13,0),
   state      VARCHAR2(15),
   country    VARCHAR2(15),  
   CONSTRAINT pk_dept PRIMARY KEY (deptno));

CREATE JSON RELATIONAL DUALITY VIEW dept_dv AS
  SELECT JSON {'_id'      : d.deptno,
               'deptName' : d.dname,
               'location' : {zipcode : d.code,
                             country : d.country}
    FROM dept_tab d WITH UPDATE INSERT DELETE;

Duality view dept_dv supports a collection of JSON documents that have top-level fields _id, deptName, and location. Document-identifier field _id is generated automatically for every duality view; its value uniquely identifies a given document. Field deptName takes its value from column dname of table dept_tab. The value of field location is an object with fields zipcode and country, whose values are taken from columns code and country, respectively. For example, this might be a document in the duality view's collection:


{_id      : 200,
 deptName : "HR"
 location : {zipcode : 94065,
             country : "USA"}

The documents use some of the data in the underlying table (they don't use column state), and they present it hierarchically. The duality view definition is declarative, and its form directly reflects the structure and typing of the JSON documents it supports.

Client applications and database applications can access the same duality-view data, each using the approach (document or relational) that makes sense to it.

Document-centric applications can use document APIs, such as Oracle Database API for MongoDB and Oracle REST Data Services (ORDS), or they can use database SQL/JSON^Foot 1 functions. Developers can manipulate duality-view documents realized by duality views in the ways they're used to, with their usual drivers, frameworks, tools, and development methods. In particular, they 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 relationally (directly as table rows and columns), using languages such as SQL, PL/SQL, C, and JavaScript. Developers need not adapt an existing database feature or code that makes use of table data to instead use JSON documents.

You need not completely normalize all of the data underlying a duality view. If you want to store some parts of the JSON documents supported by a duality view as JSON data, instead of breaking them down to scalar SQL column values, you can do so just by mapping those document parts to JSON-type columns in the underlying tables. This stored JSON data is used as is in the documents, for both reading and writing. A JSON-type column, like any other column underlying a duality view, can be shared across views.

An underlying column of an ordinary scalar SQL data type produces scalar JSON values in the documents supported by the view. A column of SQL data type JSON can produce JSON values of any kind (scalar, object, or array) in the documents, and those values can be schemaless or JSON Schema-based (to enforce particular structure 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.

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.

A duality view and its supported documents can be read-only or completely or partially updatable, depending on how you define the view. You define updatability declaratively (what/where, not how), using SQL or Oracle AI Database's subset of the GraphQL language, Oracle Database Support for GraphQL Queries.

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.

Saying that a duality view supports a set of JSON documents of a particular kind (structure and typing), indicates 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)

Note:

The idea that a JSON-relational duality view provides two different views of the same data is the main point behind such views, but it isn't the whole story. Duality views are more flexible and powerful than that image suggests.

A duality view is a mapping between the fields of its supported documents and columns in its underlying tables. But this mapping need not be as straightforward as each field value directly reflecting a column value.

Some columns of an underlying table might not be mapped to any field.
Fields and their values can be automatically generated in various ways, instead of being directly mapped to columns. Their values might depend on other field values or on one or more column values in an indirect way, or they might not depend on either fields or columns (e.g., a field value could be the phase of the moon when its document is accessed).
Fields can be hidden, whether they're generated or mapped to columns. That is, a duality view can have fields that are present in the view for internal processing purposes but are not present in the view's documents.

_________________________________________________________