Oracle 23ai and 26ai Duality Views

Duality Views expose data stored in relational tables as JSON documents. The documents are materialized -- generated on demand, not stored as such. Duality views are organized both relationally and hierarchically. They combine the advantages of using JSON documents with the advantages of the relational model, while avoiding the limitations of each. 

 

Hackolade Studio is the first and only tool to facilitate the adoptions of this revolutionary feature released with Oracle 23ai.

 

Overview

In this excellent introductory video, Oracle explains that there are 2 ways to bake cake.  You may start from individual ingredients and follow a recipe (illustrations courtesy of Oracle):

 

 

 

Or you may start with a cake mix:

 

 

The analogy is that a relational database is like assembling different basic ingredients using joins, like you would when following a recipe. In contrast, the JSON document model is like using cake mix: easy to use but you cannot deviate from the mix, or use the ingredients in your fridge to combine with another recipe.

 

 

With Oracle 23ai, Oracle introduces a way to combine both approaches, hence combining the benefits of normalized tables (traditional Oracle) and the document model (like MongoDB.)

 

 

 

The idea is to create a convergence of the traditional relational/normalized approach with the more recent JSON document approach, to achieve with the combination more than the sum of the parts

 

 

Oracle call this approach "JSON-Relational Duality Views"

 

 

JSON-relational duality views combine the advantages of using JSON documents with the advantages of the relational model, while avoiding the limitations of each:

• 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.
• 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.

 

A duality view exposes data stored in relational 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 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

 

Also, duality views could be said to be a kind of ORM (Object-Relational Mapping): they too map hierarchical object data to/from relational data. But they're fundamentally different from alternative approaches. Duality views centralize the persistence format of application objects for both server-side and client-side applications — all clients, regardless of language or framework.

 

The persistence model presents two aspects for the same data: table and document. Server-side code can manipulate relational data in tables; client-side code can manipulate a set of documents. Client code need only convert its programming objects to/from JSON, which is familiar and easy. A duality view automatically persists JSON as relational data. There's no need for any separate mapper — the duality view is the mapping.

 

 

 

Oracle Duality Views in Hackolade Studio

The Oracle plugin has been adapted, based on this comprehensive developer's guide, to natively support the data modeling of duality views, as well as the forward- and reverse-engineering of the DDL scripts.

 

While the duality views feature sounds complex, and probably is quite complex under the hood of the database engine to work efficiently, the use in Hackolade Studio is rather straight forward.

 

Previously in our Oracle plugin, we supported the creation of traditional views (materialized or not.)  Views have a set of properties and selected columns. Together they can be forward-engineered in a CREATE VIEW statement of the the DDL script. And they can be reverse-engineered with the SELECT statement parsed to generate the view structure in the model and display the ERD.

 

A duality view is just a special kind of view, also with properties and columns. The only differences are:

• nested objects of a special kind can be contained in the view
• the syntax of the DDL script introduces some new vocabulary and structure, which impacts forward- and reverse-engineering.

 

The examples below are based on this repository provided by Oracle.

 

The reverse-engineering of the relational tables in this sample DDL generates this car racing model:

 

The duality views defined with point-and-click in Hackolade Studio lead to this part of the ERD:

 

and the application automatically produces the syntactically correct part of the DDL script, with no required knowledge of the necessary syntax:

 

 

The syntax is based on this Oracle documentation page and its (always excellent) diagrams.

 

 

 

Important note: Hackolade Studio currently only supports the SQL syntax for duality views, i.e. not yet the GraphQL syntax to achieve the same result.

 

Create a duality view

As usual with Hackolade Studio, there are several different ways to do things.  To create a duality view, you first create a "generic" view, using a keyboard shortcut, the menu bar:

 

 

or the contextual menu (via mouse right-click) in either the Object Browser or the Entity Relationship Diagram pane:

 

 

 

 

Then you tick the checkbox "Duality" to display the related properties:

 

 

 

You must choose the root table from the dropdown list of tables in the schema.  You may optionally set a root table alias.

 

You may also set the Force and Editionable flags.

 

You should of course give your duality view a name.  If Naming Conventions are enabled, you provide a business name which gets transformed into a technical name according to the set rules.

 

Table tags clause

A duality view does not, itself, store any data.  All of the data that underlies its supported JSON documents (which are generated) is stored in tables underlying the view. For a duality view to be updatable means that you can update some or all of the data in its underlying tables, and so you can update some or all of the fields in its supported documents.

 

Applications can update JSON documents supported by a duality view, if you define the view as updatable. You can specify which kinds of updating operations (update, insertion, and deletion) are allowed, for which document fields, how/when, and by whom.  An application can update a complete document, replacing the existing document. Or it can update only particular fields, in place.  There are many possible permutations and rules to fit your use case, with all the details explained in this document.

 

The update annotations are set using table tags clause constructed by optionally setting (no)check, (no)insert, (no)update, and (no)delete properties.

 

Select columns from root table

The JSON format of duality views allows to include both root-level fields as well as embedded complex objects that are the result of joins with other tables.  Let's start with the root table columns.

 

From the Object Browser, from the selected view in the ERD pane, or from the hierarchical schema tab, you must right-click to bring the contextual menu and choose the option "Pick from field list":

 

 

This brings a dialog where you can select one or more columns from the selected root table:

 

 

 

For each field, you may set a JSON key alias if it is different than the underlying column name:

 

And you may also set annotations for (no)check and (no)update for tags clause at the field level.

 

For documentation purposes, you may check the Key property for the field playing the role of primary key.  However, this information does not appear in DDL script.

 

You may modify the order of fields using the up/down arrows in the toolbar or drag-and-drop in the ERD like you would do for attributes in tables.

 

 

Add nested object or array subquery

This is the heart of the benefits of duality views.  A duality view is a JSON generation view that has a limited structure, expressly designed so that your applications can update the view, and in so doing automatically update the underlying tables.

 

A duality view and its corresponding top-level JSON object provides a hierarchy of JSON objects and arrays, which are defined in the view definition using nested SQL subqueries. Data gathered from a subquery is joined to data gathered from a parent subquery or the root table by a relationship between a primary or unique key in the parent and a foreign key in the child subquery's WHERE clause.

 

The cardinality of relationships dictates whether you use an object or an array type of subquery.

 

You may create multi-level nesting to represent recursive joins.  

 

To add a subquery, invoke the contextual menu to append a column and choose Subquery:

 

 

You should provide a name for the subquery and choose the type object or array depending on the cardinality of your join: object for 1-to-1 relationships, and array for 1-to-many relationships:

 

 

 

You must choose the child table from the dropdown list of tables in the schema.  You may optionally set a child table alias.

 

The update annotations use the table tags clause constructed by optionally setting (no)check, (no)insert, (no)update, and (no)delete properties.

 

You must enter the WHERE clause for the join.

 

Add unnested fields

In some cases, you might prefer to flatten structures instead of the nested subquery.  The unnested subquery allows to select columns in the underlying child table and present the fields in the JSON at the same level as the columns of the parent table.  This can be done at any level of the tree.

 

 

While the fields are unnested in the JSON document, the unnesting structure remains visible in the ERD and Object Browser.

 

 

or in the hierarchical schema tab

 

 

Forward-engineer DDL script

The application automatically produces the syntactically correct DDL script, with no required knowledge of the necessary syntax:

 

 

 

which can be exported to a file, copied/pasted, or applied directly to a database instance.

 

Reverse-engineer an existing instance or a DDL file

Hackolade Studio can reverse-engineer DDL files including duality views defines in SQL syntax.  This is particularly useful if it is not possible to connect the application to the Oracle database instance.  

 

The drawback of DDL file-based reverse-engineering however is that, in the event of columns defined with a JSON data type or LOB containing JSON documents, the application will not have a chance to sample JSON in order to infer the schema.  Such information never appears in DDLs and only Hackolade Studio is able to infer the schema of JSON in RDBMS.

 

The most effective method to get a full picture of the data structure is to connect Hackolade Studio to the database instance and launch the reverse-engineering process, which produces an ERD representation of the instance.