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.

Table-centric use case: You have, or you will develop, one or more applications that are table-centric; that is, they primarily use normalized relational data. At the same time, you have a need to present JSON-document views of some of your table data to (often client) applications. You sometimes want the views and their documents to be updatable, partially or wholly.

The other main use case for duality views is described in Document-Centric Use Case for JSON-Relational Duality: document-centric application development, where developers start with JSON documents that they want to work with (typically based on application objects), or at least with a model of those documents. In that context, creating duality views involves these steps:

1. Analyzing the existing (or expected) document sets to define normalized entities and relations that represent the underlying logic of the different kinds of documents. (See Car-Racing Example, Entity Relationships.)

2. Defining relational tables that can implement those entities. (See Car-Racing Example, Tables.)

3. Defining different duality views over those tables, to support/generate the different kinds of documents. (See Creating Duality Views.)

On its own, step 3 represents the table-centric use case for JSON-relational duality: creating duality views over existing relational data. Instead of starting with one or more sets of documents, and analyzing them to come up with relational tables to underlie them (steps 1 and 2), you directly define duality views, and the document collections they support, based on tables that already exist.

It's straightforward to define a duality view that's based on existing relational data, because that data has already undergone data analysis and factoring (normalization). So it's easy to adapt or define a document-centric application to reuse existing relational data as a set of JSON documents. This alone is a considerable advantage of the duality between relational and JSON data. You can easily make the wide world of existing relational data available as sets of JSON documents.

We can look at a simple SQL example right away, without explaining everything involved, just to get an idea of how easy it can be to create and use a duality view.

Assume that we have table department, with deptno as its primary-key column:

CREATE TABLE department
  (deptno     NUMBER(2,0),   
   dname      VARCHAR2(14),   
   loc        VARCHAR2(13),   
   CONSTRAINT pk_dept PRIMARY KEY (deptno));

Here's all we need to do, to create a duality view (department_dv) over that one table. The view exposes the table data as a collection of JSON documents with fields _id, departmentName, and location.

CREATE JSON RELATIONAL DUALITY VIEW department_dv AS
  SELECT JSON {'_id'            : d.deptno,
               'departmentName' : d.dname,
               'location'       : d.loc}
    FROM department d WITH UPDATE INSERT DELETE;

In Creating Duality Views, SQL statement CREATE JSON RELATIONAL DUALITY VIEW is explained in detail. Suffice it to say here that the syntax for creating the duality view selects the columns of table department to generate JSON objects as the documents supported by the view.

Columns deptno, dname, and loc are mapped, for document generation, to document fields _id, departmentName, and location, respectively.^Foot 1 The documents supported by the duality view have a single JSON object, with only those three fields. (The JSON {…} syntax indicates the object with those fields.)

The annotations WITH UPDATE INSERT DELETE define the duality view as completely updatable: applications can update, insert, and delete documents, which in turn updates the underlying tables.

We can immediately query (select) documents from the duality view. Each document looks like this:

{_id            : <department number>,
 departmentName : <department-name string>,
 location       : <location string>}

Suppose now that we also have table employees, defined as follows. It has primary-key column empno; and it has foreign-key column deptno, which references column deptno of table dept.

CREATE TABLE employee
  (empno    NUMBER(4,0),   
   ename    VARCHAR2(10),   
   job      VARCHAR2(9),   
   mgr      NUMBER(4,0),   
   hiredate DATE,   
   sal      NUMBER(7,2),   
   deptno   NUMBER(2,0),   
   CONSTRAINT pk_emp PRIMARY KEY (empno),   
   CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES department (deptno));

In this case we can define a slightly more complex department duality view, dept_w_employees_dv, which includes some data for the employees of the department:

CREATE JSON RELATIONAL DUALITY VIEW dept_w_employees_dv AS
  SELECT JSON {'_id'            : d.deptno,
               'departmentName' : d.dname,
               'location'       : d.loc,
               'employees'      :
                 [ SELECT JSON {'employeeNumber' :e.empno,
                                'name' : e.ename}
                     FROM employee e
                     WHERE e.deptno = d.deptno ]}
    FROM department d WITH UPDATE INSERT DELETE;

Here, we see that each department object has also an employees field, whose value is an array (note the JSON […] syntax) of employee objects (the inner JSON {…} syntax). The values of the employee-object fields are taken from columns employeeNumber and name of the employee table.

The tables are joined with the WHERE clause, to produce the employee information in the documents: the department of each employee listed must have the same number as the department represented by the document.

A simple query of the view returns documents that look like this:

{_id            : <department number>,
 departmentName : <department-name string>,
 location       : <location string>
 employees      :
   [ {employeeNumber : <employee number>,
      name           : <employee name>} ]}