You use SQL with (1) SQL/JSON generation-function queries or (2) GraphQL queries to create JSON-relational duality views. Example team, driver, and race duality views are created to provide the JSON documents used by a car-racing application.

(To get a quick sense of how easy it can be to create a duality view over existing relational data, see Table-Centric Use Case for JSON-Relational Duality.)

The views created here are based on the data in the related tables driver, race, and team, which underlie the views driver_dv, race_dv, and team_dv, respectively, as well as mapping table driver_race_map, which underlies views driver_dv and race_dv.

A duality view supports JSON documents, each of which has a top-level JSON object. You can interact with a duality view as if it were a table with a single column of JSON data type.

A duality view and its corresponding top-level JSON object provide 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 table by a relationship between the corresponding identifying columns in the subquery's WHERE clause. These columns can have, but need not have, primary-key and foreign-key constraints.

An identifying column is a primary-key column, an identity column, a column with a unique constraint, or a column with a unique index.

You can create a read-only, non-duality SQL view using SQL/JSON generation functions directly (see Read-Only Views Based On JSON Generation in Oracle Database JSON Developer’s Guide).

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. All duality views share the same limitations that allow for this, even those that are read-only.

In general, columns from tables underlying a duality view are mapped to fields in the documents supported by the view; that is, column values are used as field values.

Because a duality view and its supported documents can generally be updated, which updates data in its underlying tables, each table must have one or more identifying columns, whose values collectively identify uniquely the table row used to generate the corresponding field values. All occurrences of a given table in a duality-view definition must use the same set of identifier columns.

A duality view has only one payload column, named DATA, of JSON data type, which is generated from underlying table data. Each row of a duality view thus contains a single JSON object, the top-level object of the view definition. This object acts as a JSON document supported by the view.

In addition to the payload document content, that is, the application content per se, a document's top-level object always has the automatically generated and maintained document-handling field _metadata. Its value is an object with these fields:

• etag — A unique identifier for a specific version of the document, as a string of hexadecimal characters.

  This identifier is constructed as a hash value of the document content (payload), that is, all document fields except field _metadata. (More precisely, all fields whose underlying columns are implicitly or explicitly annotated CHECK, meaning that those columns contribute to the ETAG value.)

  This ETAG value lets an application determine whether the content of a particular version of a document is the same as that of another version. This is used, for example, to implement optimistic concurrency. See Using Optimistic Concurrency Control With Duality Views.

• asof — The latest system change number (SCN) for the JSON document, as a JSON number. This records the last logical point in time at which the document was generated.

  The SCN can be used to query other database objects (duality views, tables) at the exact point in time that a given JSON document was retrieved from the database. This provides consistency across database reads. See Using the System Change Number (SCN) of a JSON Document

Besides the payload column DATA, a duality view also contains two hidden columns, which you can access from SQL:

• ETAG — This 16-byte RAW column holds the ETAG value for the current row of column DATA. That is, it holds the data used for the document metadata field etag.

• RESID — This variable-length RAW column holds an object identifier that uniquely identifies the document that is the content of the current row of column DATA. The column value is a concatenated binary encoding of the identifier columns of the root table.

You create a duality view using SQL DDL statement CREATE JSON RELATIONAL DUALITY VIEW, whose syntax allows for the optional use of a subset of the GraphQL language.

For convenience, each time you create a duality view a synonym is automatically created for the view name you provide. If the name you provide is unquoted then the synonym is the same name, but quoted. If the name you provide is quoted then the synonym is the same name, but unquoted. If the quoted name contains one or more characters that aren't allowed in an unquoted name then no synonym is created. The creation of a synonym means that the name of a duality view is, in effect, always case-sensitive regardless of whether it's quoted. See CREATE SYNONYM in Oracle Database SQL Language Reference.

_________________________________________________________

• Creating Car-Racing Duality Views Using SQL
  Team, driver, and race duality views for the car-racing application are created using SQL.
• Creating Car-Racing Duality Views Using GraphQL
  Team, driver, and race duality views for the car-racing application are created using GraphQL.
• WHERE Clauses in Duality-View Tables
  When creating a JSON-relational duality view, you can use simple tests in WHERE clauses to not only join underlying tables but to select which table rows are used to generate JSON data. This allows fine-grained control of the data to be included in a JSON document supported by a duality view.