1. JSON-Relational Duality Developer's Guide
  2. Introduction To Car-Racing Duality Views Example

2 Introduction To Car-Racing Duality Views Example

Data for Formula 1 car races is used here to present the features of JSON-relational duality views. This use-case example starts from an analysis of the kinds of JSON documents needed. It then defines corresponding entities and their relationships, relational tables, and duality views built on those tables.

Note:

An alternative approach to creating duality views is available to migrate an application that has existing sets of related documents, so that it uses duality views.

For that you can use the JSON-to-duality migrator, which automatically infers and generates the appropriate duality views. No need to manually analyze the different kinds of documents to discover implicit entities and relationships, and then define and populate the relevant duality views and their underlying normalized tables.

The migrator does all of that for you. By default, whatever document parts can be shared within or across views are shared, and the views are defined for maximum updatability.

See From JSON To Duality.

For the car-racing example we suppose a document-centric application that uses three kinds of JSON documents: driver, race, and team. Each of these kinds shares some data with another kind. For example:

  • A driver document includes, in its information about a driver, identification of the driver's team and information about the races the driver has participated in.

  • A race document includes, in its information about a particular race, information about the podium standings (first-, second-, and third-place winners), and the results for each driver in the race. Both of these include driver and team names. The racing data is for a single season of racing.

  • A team document includes, in its information about a team, information about the drivers on the team.

Operations the application might perform on this data include the following:

  • Adding or removing a driver, race, or team to/from the database

  • Updating the information for a driver, race, or team

  • Adding a driver to a team, removing a driver from a team, or moving a driver from one team to another

  • Adding race results to the driver and race information

The intention in this example is that all common information be shared, so that, say, the driver with identification number 302 in the driver duality view is the same as driver number 302 in the team view.

You specify the sharing of data that's common between two duality views by defining relations between them. You do this by specifying primary and foreign keys for the tables that underlie the duality views.

When you define a given duality view you can control whether it's possible to insert into, delete from, or update the documents supported by the view and, overriding those constraints, whether it's possible to insert, delete, or update a given field in a supported document. By default, a duality view is read-only: no inserting, deleting, or updating documents.

  • Car-Racing Example, JSON Documents
    The car-racing example has three kinds of documents: a team document, a driver document, and a race document.
  • Car-Racing Example, Entity Relationships
    Driver, car-race, and team entities are presented, together with the relationships among them. You define entities that correspond to your application documents in order to help you determine the tables needed to define the duality views for your application.
  • Car-Racing Example, Tables
    Normalized entities are modeled as database tables. Entity relationships are modeled as links (constraints) between primary-key and foreign-key columns. Tables team, driver, and race are used to implement the duality views that provide and support the team, driver, and race JSON documents used by the car-racing application.
  • Car-Racing Example, Duality Views
    Team, driver, and race duality views provide and support the team, driver, and race JSON documents used by a car-racing application.

See Also:

2.1 Car-Racing Example, JSON Documents

The car-racing example has three kinds of documents: a team document, a driver document, and a race document.

A document supported by a duality view always includes, at its top (root) level, a document-identifier field, _id, that corresponds to the primary-key columns of the tables that underlie the view. See Document-Identifier Field for Duality Views. (In the car-racing example each such table has a single primary-key column.)

The following naming convention is followed in this documentation:

  • The document-identifier field (_id) of each kind of document (team, driver, or race) corresponds to the root-table primary-key column of the duality view that supports those documents. For example, field _id of a team document corresponds to primary-key column team_id of table team, which is the root table underlying duality view team_dv.

  • Documents of one kind (e.g. team), supported by one duality view (e.g. team_dv) can include other fields named ...Id (e.g. driverId), which represent foreign-key references to primary-key columns in tables underlying other duality views — columns that contain data that's shared. For example, in a team document, field driverId represents a foreign key that refers to the document-identifier field (_id) of a driver document.

Note:

Only the application-logic document content, or payload of each document, is shown here. That is, the documents shown here do not include the automatically generated and maintained, top-level field _metadata (whose value is an object with fields etag and asof). However, this document-handling field is always included in documents supported by a duality view. See Car-Racing Example, Duality Views for information about field _metadata.

Example 2-1 A Team Document

A team document includes information about the drivers on the team, in addition to information that's relevant to the team but not necessarily relevant to its drivers.

  • Top-level field _id uniquely identifies a team document. It is the document-identifier field. Column team_id of table team corresponds to this field; it is the table's primary key.

  • The team information that's not shared with driver documents is in field _id and top-level fields name and points.

  • The team information that's shared with driver documents is in fields driverId, name, and points, under field driver. The value of field driverId is that of the document-identifier field (_id) of a driver document. 

{"_id"    : 302,
 "name"   : "Ferrari",
 "points" : 300,
 "driver" : [ {"driverId" : 103,
               "name"     : "Charles Leclerc",
               "points"   : 192},
              {"driverId" : 104,
               "name"     : "Carlos Sainz Jr",
               "points"   : 118} ]}

Example 2-2 A Driver Document

A driver document includes identification of the driver's team and information about the races the driver has participated in, in addition to information that's relevant to the driver but not necessarily relevant to its team or races.

  • Top-level field _id uniquely identifies a driver document. It is the document-identifier field. Column driver_id of the driver table corresponds to this field; it is that table's primary key.

  • The driver information that's not shared with race or team documents is in fields _id, name, and points.

  • The driver information that's shared with race documents is in field race. The value of field raceId is that of the document-identifier field (_id) of a race document.

  • The driver information that's shared with a team document is in fields such as teamId, whose value is that of the document-identifier field (_id) of a team document.

Two alternative versions of a driver document are shown, with and without nested team and race information.

Driver document, with nested team and race information:

Field teamInfo contains the nested team information (fields teamId and name). Field raceInfo contains the nested race information (fields raceId and name). 

{"_id"      : 101,
 "name"     : "Max Verstappen",
 "points"   : 258,
 "teamInfo" : {"teamId" : 301, "name" : "Red Bull"},
 "race"     : [ {"driverRaceMapId" : 3,
                 "raceInfo"        : {"raceId" : 201,
                                      "name"   : "Bahrain Grand Prix"},
                 "finalPosition"   : 19},
                {"driverRaceMapId" : 11,
                 "raceInfo"        : {"raceId" : 202,
                                      "name"   : "Saudi Arabian Grand Prix"},
                 "finalPosition"   : 1} ]}

Driver document, without nested team and race information:

Fields teamId and team are not nested in a teamInfo object. Fields raceId and name are not nested in a raceInfo object. 

{"_id"      : 101,
 "name"     : "Max Verstappen",
 "points"   : 25,
 "teamId"   : 301,
 "team"     : "Red Bull",
 "race"     : [ {"driverRaceMapId" : 3,
                 "raceId"          : 201,
                 "name"            : "Bahrain Grand Prix",
                 "finalPosition"   : 19},
                {"driverRaceMapId" : 11,
                 "raceId"          : 202,
                 "name"            : "Saudi Arabian Grand Prix",
                 "finalPosition"   : 1} ]}

Example 2-3 A Car-Race Document

A race document includes, in its information about a particular race, information about the podium standings (first, second, and third place), and the results for each driver in the race. The podium standings include the driver and team names. The result for each driver includes the driver's name.

Both of these include driver and team names.

  • Top-level field _id uniquely identifies a race document. It is the document-identifier field. Column race_id of the race table corresponds to this field; it is that table's primary key.

  • The race information that's not shared with driver or team documents is in fields _id, name (top-level), laps, date, time, and position.

  • The race information that's shared with driver documents is in fields such as driverId, whose value is that of the document-identifier field (_id) of a driver document.

  • The race information that's shared with team documents is in field team (under winner, firstRunnerUp, and secondRunnerUp, which are under podium).

Two alternative versions of a race document are shown, with and without nested driver information.

Race document, with nested driver information:

{"_id"    : 201,
 "name"   : "Bahrain Grand Prix",
 "laps"   : 57,
 "date"   : "2022-03-20T00:00:00",
 "podium" : {"winner"         : {"name" : "Charles Leclerc",
                                 "team" : "Ferrari",
                                 "time" : "02:00:05.3476"},
             "firstRunnerUp"  : {"name" : "Carlos Sainz Jr",
                                 "team" : "Ferrari",
                                 "time" : "02:00:15.1356"},
             "secondRunnerUp" : {"name" : "Max Verstappen",
                                 "team" : "Red Bull",
                                 "time" : "02:01:01.9253"}},
 "result" : [ {"driverRaceMapId" : 3,
               "position"        : 1,
               "driverInfo"      : {"driverId" : 103,
                                    "name"     : "Charles Leclerc"},
              {"driverRaceMapId" : 4,
               "position"        : 2,
               "driverInfo"      : {"driverId" : 104,
                                    "name"     : "Carlos Sainz Jr"},
              {"driverRaceMapId" : 9,
               "position"        : 3,
               "driverInfo"      : {"driverId" : 101,
                                    "name"     : "Max Verstappen"},
              {"driverRaceMapId" : 10,
               "position"        : 4,
               "driverInfo"      : {"driverId" : 102,
                                    "name"     : "Sergio Perez"} ]}

Race document, without nested driver information:

{"_id"    : 201,
 "name"   : "Bahrain Grand Prix",
 "laps"   : 57,
 "date"   : "2022-03-20T00:00:00",
 "podium" : {"winner"         : {"name" : "Charles Leclerc",
                                 "team" : "Ferrari",
                                 "time" : "02:00:05.3476"},
             "firstRunnerUp"  : {"name" : "Carlos Sainz Jr",
                                 "team" : "Ferrari",
                                 "time" : "02:00:15.1356"},
             "secondRunnerUp" : {"name" : "Max Verstappen",
                                 "team" : "Red Bull",
                                 "time" : "02:01:01.9253"}},
 "result" : [ {"driverRaceMapId" : 3,
               "position"        : 1,
               "driverId"        : 103,
               "name"            : "Charles Leclerc"},
              {"driverRaceMapId" : 4,
               "position"        : 2,
               "driverId"        : 104,
               "name"            : "Carlos Sainz Jr"},
              {"driverRaceMapId" : 9,
               "position"        : 3,
               "driverId"        : 101,
               "name"            : "Max Verstappen"},
              {"driverRaceMapId" : 10,
               "position"        : 4,
               "driverId"        : 102,
               "name"            : "Sergio Perez"} ]}

Related Topics

Parent topic: Introduction To Car-Racing Duality Views Example

2.2 Car-Racing Example, Entity Relationships

Driver, car-race, and team entities are presented, together with the relationships among them. You define entities that correspond to your application documents in order to help you determine the tables needed to define the duality views for your application.

From the documents to be used by your application you can establish entities and their relationships. Each entity corresponds to a document type: driver, race, team.

Unlike the corresponding documents, the entities we use have no content overlap — they're normalized. The content of an entity (what it represents) is only that which is specific to its corresponding document type; it doesn't include anything that's also part of another document type.

  • The driver entity represents only the content of a driver document that's not in a race or team document. It includes only the driver's name and points, corresponding to document fields name and points.

  • The race entity represents only the content of a race document that's not in a driver document or a team document. It includes only the race's name, number of laps, date, and podium information, corresponding to document fields name, laps, date, and podium.

  • The team entity represents only the content of a team document that's not in a document or race document. It includes only the team's name and points, corresponding to document fields name and points.

Two entities are related according to their cardinality. There are three types of such relationships:Foot 1

One-to-one (1:1)
An instance of entity A can only be associated with one instance of entity B. For example, a driver can only be on one team.
One-to-many (1:N)
An instance of entity A can be associated with one or more instances of entity B. For example, a team can have many drivers.
Many-to-many (N:N)
An instance of entity A can be associated with one or more instances of entity B, and conversely. For example, a race can have many drivers, and a driver can participate in many races.

See Also:

Entity-relationship model

A many-to-one (N:1) relationship is just a one-to-many relationship looked at from the opposite viewpoint. We use only one-to-many.

See Figure 2-1. An arrow indicates the relationship direction, with the arrowhead pointing to the second cardinality. For example, the 1:N arrow from entity team to entity driver points toward driver, to show that one team relates to many drivers.

Figure 2-1 Car-Racing Example, Directed Entity-Relationship Diagram (1)

Description of Figure 2-1 follows
Description of "Figure 2-1 Car-Racing Example, Directed Entity-Relationship Diagram (1)"

A driver can only be associated with one team (1:1). A team can be associated with multiple drivers (1:N). A driver can be associated with multiple races (N:N). A race can be associated with multiple drivers (N:N).

Related Topics

See Also:

Database normalization (Wikipedia)

Parent topic: Introduction To Car-Racing Duality Views Example

2.3 Car-Racing Example, Tables

Normalized entities are modeled as database tables. Entity relationships are modeled as links (constraints) between primary-key and foreign-key columns. Tables team, driver, and race are used to implement the duality views that provide and support the team, driver, and race JSON documents used by the car-racing application.

The normalized entities have no content overlap. But we need the database tables that implement the entities to overlap logically, in the sense of a table referring to some content that is stored in another table. To realize this we add columns that are linked to other tables using foreign-key constraints. It is these foreign-key relations among tables that implement their sharing of common content.

The tables used to define a duality view must satisfy these requirements (otherwise an error is raised when you try to create the view):

  • The top-level (root) table for the view must have a primary key, composed of one or more columns that together uniquely identify a table row. This prevents any ambiguity that could arise from using a NULLable unique key or a unique key that has some NULL columns.

    The primary-key column values correspond to the value of the document-identifier field, _id, of the JSON document that the table is designed to support — see Document-Identifier Field for Duality Views. (There is only one primary-key column for each of the tables used in the car-racing example.)

  • Each of the other tables used to define a duality view must also have a primary key or a unique key. A unique key is a set of one or more columns that uniquely identify a row in the table. If there is no primary key then at least one column of the unique key must not be NULL.

  • Each primary key and each unique key must have a unique index defined on it. Oracle recommends that you also define an index on each foreign-key column. References (links) between primary and foreign keys must be defined, but they need not be enforced.

    Note:

    Primary and unique indexes are generally created implicitly when you define primary-, and unique-key integrity constraints. But this is not guaranteed, and indexes can be dropped after their creation. It's up to you to ensure that the necessary indexes are present. See Creating Indexes in Oracle Database Administrator’s Guide.

Like unique keys, primary keys and foreign keys can be composite: composed of multiple columns. In this documentation we generally speak of them as single-column keys, but keep this possibility in mind wherever keys are mentioned.

In general, a value in a foreign-key column can be NULL. Besides the above requirements, if you want a foreign-key column to not be NULLable, then mark it as NOT NULL in the table definition.

In the car-racing example, entities team, driver, and race are implemented by tables team, driver, and race, which have the following columns:

  • team table:

    • team_id — primary key

    • name — unique key

    • points

  • driver table:

    • driver_id — primary key

    • name — unique key

    • points

    • team_id — foreign key that links to column team_id of table team

  • race table:

    • race_id — primary key

    • name — unique key (so the table has no duplicate rows: there can't be two races with the same name)

    • laps

    • race_date

    • podium

The logic of the car-racing application mandates that there be only one team with a given team name, only one driver with a given driver name, and only one race with a given race name, so column name of each of these tables is made a unique key. (This in turn means that there is only one team document with a given name field value, only one driver document with a given name, and only one race document with a given name.)

Table driver has an additional column, team_id, which is data that's logically shared with table team (it corresponds to document-identifier field _id of the team document). This sharing is defined by declaring the column to be a foreign key in table driver, which links to (primary-key) column team_id of table team. That link implements both the 1:1 relationship from driver to team and the 1:N relationship from team to driver.

But what about the other sharing: the race information in a driver document that's shared with a race document, and the information in a race document that's shared with a driver document or with a team document?

That information sharing corresponds to the many-to-many (N:N) relationships between entities driver and race. The database doesn't implement N:N relationships directly. Instead, we need to add another table, called a mapping table (or an associative table), to bridge the relationship between tables driver and race. A mapping table includes, as foreign keys, the primary-key columns of the two tables that it associates.

An N:N entity relationship is equivalent to a 1:N relationship followed by a 1:1 relationship. We use this equivalence to implement an N:N entity relationship using database tables, by adding mapping table driver_race_map between tables driver and race.

Figure 2-2 is equivalent to Figure 2-1. Intermediate entity d-r-map is added to expand each N:N relationship to a 1:N relationship followed by a 1:1 relationship.Foot 2

Figure 2-2 Car-Racing Example, Directed Entity-Relationship Diagram (2)

Description of Figure 2-2 follows
Description of "Figure 2-2 Car-Racing Example, Directed Entity-Relationship Diagram (2)"

Mapping table driver_race_map implements intermediate entity d-r-map. It has the following columns:

  • driver_race_map_id — primary key

  • race_id — (1) foreign key that links to primary-key column race_id of table race and (2) unique key (so the table has no duplicate rows: there can't be two entries for the same driver for a particular race)

  • driver_id — foreign key that links to primary-key column driver_id of table driver

  • position

Together with the relations defined by their foreign-key and primary-key links, the car-racing tables form a dependency graph. This is shown in Figure 2-3.

Example 2-4 Creating the Car-Racing Tables

This example creates each table with a primary-key column, whose values are automatically generated as a sequence of integers, and a unique-key column, name. This implicitly also creates unique indexes on the primary-key columns. The example also creates foreign-key indexes.

Column podium of table race has data type JSON. Its content is flexible: it need not conform to any particular structure or field types. Alternatively, its content could be made to conform to (that is, validate against) a particular JSON schema. 


CREATE TABLE team
  (team_id    INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
   name       VARCHAR2(255) NOT NULL UNIQUE,
   points     INTEGER NOT NULL,
   CONSTRAINT team_pk PRIMARY KEY(team_id));

CREATE TABLE driver 
  (driver_id  INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
   name       VARCHAR2(255) NOT NULL UNIQUE,
   points     INTEGER NOT NULL,
   team_id    INTEGER,
   CONSTRAINT driver_pk PRIMARY KEY(driver_id),
   CONSTRAINT driver_fk FOREIGN KEY(team_id) REFERENCES team(team_id));

CREATE TABLE race
  (race_id    INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
   name       VARCHAR2(255) NOT NULL UNIQUE,
   laps       INTEGER NOT NULL,
   race_date  DATE,
   podium     JSON,
   CONSTRAINT race_pk PRIMARY KEY(race_id));

-- Mapping table, to bridge the tables DRIVER and RACE.
--
CREATE TABLE driver_race_map
  (driver_race_map_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
   race_id            INTEGER NOT NULL,
   driver_id          INTEGER NOT NULL,
   position           INTEGER,
   CONSTRAINT driver_race_map_uk  UNIQUE (race_id, driver_id),
   CONSTRAINT driver_race_map_pk  PRIMARY KEY(driver_race_map_id),
   CONSTRAINT driver_race_map_fk1 FOREIGN KEY(race_id)
                                    REFERENCES race(race_id),
   CONSTRAINT driver_race_map_fk2 FOREIGN KEY(driver_id)
                                    REFERENCES driver(driver_id));
-- Create foreign-key indexes
--
CREATE INDEX driver_fk_idx ON driver (team_id);
CREATE INDEX driver_race_map_fk1_idx ON driver_race_map (race_id);
CREATE INDEX driver_race_map_fk2_idx ON driver_race_map (driver_id);

Note:

Primary-key, unique-key, and foreign-key integrity constraints must be defined for the tables that underlie duality views (or else an error is raised), but they need not be enforced.

In some cases you might know that the conditions for a given constraint are satisfied, so you don't need to validate or enforce it. You might nevertheless want the constraint to be present, to improve query performance. In that case, you can put the constraint in the RELY state, which asserts that the constraint is believed to be satisfied. See RELY Constraints in a Data Warehouse in Oracle Database Data Warehousing Guide.

You can also make a foreign key constraint DEFERRABLE, which means that the validity check is done at the end of a transaction. See Deferrable Constraints in Oracle Database Concepts

Note:

The SQL data types allowed for a column in a table underlying a duality view are BINARY_DOUBLE, BINARY_FLOAT, BLOB, BOOLEAN, CHAR, CLOB, DATE, JSON, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, NCHAR, NCLOB, NUMBER, NVARCHAR2, VARCHAR2, RAW, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and VECTOR. An error is raised if you specify any other column data type.

Related Topics

See Also:

Parent topic: Introduction To Car-Racing Duality Views Example

2.4 Car-Racing Example, Duality Views

Team, driver, and race duality views provide and support the team, driver, and race JSON documents used by a car-racing application.

The views 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 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.

You can create a regular, read-only SQL view using SQL/JSON generation functions directly, without creating a duality view (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.

Note:

For input of data types CLOB and BLOB to SQL/JSON generation functions, an empty instance is distinguished from SQL NULL. It produces an empty JSON string (""). But for input of data types VARCHAR2, NVARCHAR2, and RAW, Oracle SQL treats an empty (zero-length) value as NULL, so do not expect such a value to produce a JSON string.

A column of data in a table underlying a duality view is used as input to SQL/JSON generation functions to generate the JSON documents supported by the view. An empty value in the column can thus result in either an empty string or a SQL NULL value, depending on the data type of the column.

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 primary-key columns of the root table.

You can create duality views using SQL or a subset of the GraphQL language.

Related Topics

See Also:

Parent topic: Introduction To Car-Racing Duality Views Example

2.4.1 Creating Car-Racing Duality Views Using SQL

Team, driver, and race duality views for the car-racing application are created using SQL.

The SQL statements here that define the car-racing duality views use a simplified syntax which makes use of the JSON-type constructor function, JSON, as shorthand for using SQL/JSON generation functions to construct (generate) JSON objects and arrays. JSON {…} is simple syntax for using function json_object, and JSON […] is simple syntax for using function json_array or json_arrayagg.

Occurrences of JSON {…} and JSON […] that are embedded within other such occurrences can be abbreviated as just {…} and […], it being understood that they are part of an enclosing JSON generation function.

The arguments to generation function json_object are definitions of individual JSON-object members: a field name, such as points, followed by a colon (:) or keyword IS, followed by the defining field value (for example, 110) — 'points' : 110 or 'points' IS 110. Note that the JSON field names are enclosed with single-quote characters (').

Some of the field values are defined directly as column values from the top-level table for the view: table driver (alias d) for view driver_dv, table race (alias r) for view race_dv, and table team (alias t) for view team_dv. For example: 'name' : d.name, for view driver_dv defines the value of field name as the value of column name of the driver table.

Other field values are defined using a subquery (SELECT ...) that selects data from one of the other tables. That data is implicitly joined, to form the view data.

Some of the subqueries use the syntax JSON {…}, which defines a JSON object with fields defined by the definitions enclosed by the braces ({, }). For example, JSON {'_id' : r.race_id, 'name' : r.name} defines a JSON object with fields _id and name, defined by the values of columns race_id and name, respectively, from table r (race).

Other subqueries use the syntax JSON […], which defines a JSON array whose elements are the values that the subquery returns, in the order they are returned. For example, [ SELECT JSON {…} FROM driver WHERE ... ] defines a JSON array whose elements are selected from table driver where the given WHERE condition holds.

Duality views driver_dv and race_dv each nest data from the mapping table driver_race_map. Two versions of each of these views are defined, one of which includes a nested object and the other of which, defined using keyword UNNEST, flattens that nested object to just include its fields directly. For view driver_dv the nested object is the value of field teamInfo. For view race_dv the nested object is the value of field driverInfo. (If you like, you can use keyword NEST to make explicit the default behavior of nesting.)

In most of this documentation, the car-racing examples use the view and document versions without these nested objects.

Nesting is the default behavior for fields from tables other than the root table. Unnesting is the default behavior for fields from the root table. You can use keyword NEST if you want to make the default behavior explicit — see Example 8-1 for an example. Note that you cannot nest fields that correspond to primary-key columns of the root table; an error is raised if you try.

Example 2-5 Creating Duality View TEAM_DV Using SQL

This example creates a duality view where the team objects look like this — they contain a field driver whose value is an array of nested objects that specify the drivers on the team: 

{"_id" : 301, "name" : "Red Bull", "points" : 0, "driver" : [...]}

(The view created is the same as that created using GraphQL in Example 2-10.) 

CREATE JSON RELATIONAL DUALITY VIEW team_dv AS
  SELECT JSON {'_id'   : t.team_id,
               'name'   : t.name,
               'points' : t.points,
               'driver' :
                 [ SELECT JSON {'driverId' : d.driver_id,
                                'name'     : d.name,
                                'points'   : d.points WITH NOCHECK}
                     FROM driver d WITH INSERT UPDATE
                     WHERE d.team_id = t.team_id ]}
  FROM team t WITH INSERT UPDATE DELETE;

Example 2-6 Creating Duality View DRIVER_DV, With Nested Team Information Using SQL

This example creates a duality view where the driver objects look like this — they contain a field teamInfo whose value is a nested object with fields teamId and (team) name: 

{"_id"      : 101,
 "name"     : "Max Verstappen",
 "points"   : 0,
 "teamInfo" : {"teamId" : 103, "name" : "Red Bull"},
 "race"     : [...]}
CREATE JSON RELATIONAL DUALITY VIEW driver_dv AS
  SELECT JSON {'_id'     : d.driver_id,
               'name'     : d.name,
               'points'   : d.points,
               'teamInfo' :
                 (SELECT JSON {'teamId' : t.team_id,
                               'name'   : t.name WITH NOCHECK}
                    FROM team t WITH NOINSERT NOUPDATE NODELETE
                    WHERE t.team_id = d.team_id),
               'race'     :
                 [ SELECT JSON {'driverRaceMapId' : drm.driver_race_map_id,
                                'raceInfo'        :
                                  (SELECT JSON {'raceId' : r.race_id,
                                                'name'   : r.name}
                                     FROM race r WITH NOINSERT NOUPDATE NODELETE
                                     WHERE r.race_id = drm.race_id),
                                'finalPosition'   : drm.position}
                    FROM driver_race_map drm WITH INSERT UPDATE NODELETE
                    WHERE drm.driver_id = d.driver_id ]}
    FROM driver d WITH INSERT UPDATE DELETE;

Example 2-7 Creating Duality View DRIVER_DV, With Unnested Team Information Using SQL

This example creates a duality view where the driver objects look like this — they don't contain a field teamInfo whose value is a nested object with fields teamId and name. Instead, the data from table team is incorporated at the top level, with the team name as field team. 

{"_id"    : 101,
 "name"   : "Max Verstappen",
 "points" : 0,
 "teamId" : 103,
 "team"  : "Red Bull",
 "race"   : [...]}

Instead of using 'teamInfo' : to define top-level field teamInfo with an object value resulting from the subquery of table team, the view definition precedes that subquery with keyword UNNEST, and it uses the data from column name as the value of field team. In all other respects, this view definition is identical to that of Example 2-6.

(The view created is the same as that created using GraphQL in Example 2-11.) 

CREATE JSON RELATIONAL DUALITY VIEW driver_dv AS
  SELECT JSON {'_id'     : d.driver_id,
               'name'     : d.name,
               'points'   : d.points,
               UNNEST
                 (SELECT JSON {'teamId' : t.team_id,
                               'team'   : t.name WITH NOCHECK}
                    FROM team t WITH NOINSERT NOUPDATE NODELETE
                    WHERE t.team_id = d.team_id),
               'race'     :
                 [ SELECT JSON {'driverRaceMapId' : drm.driver_race_map_id,
                                UNNEST
                                  (SELECT JSON {'raceId' : r.race_id,
                                                'name'   : r.name}
                                     FROM race r WITH NOINSERT NOUPDATE NODELETE
                                     WHERE r.race_id = drm.race_id),
                                'finalPosition'   : drm.position}
                    FROM driver_race_map drm WITH INSERT UPDATE NODELETE
                    WHERE drm.driver_id = d.driver_id ]}
    FROM driver d WITH INSERT UPDATE DELETE;

Note that if for some reason you wanted (non-primary-key) fields from the root table, driver, to be in a nested object, you could do that. For example, this would nest fields name and points in a driverInfo object: You could optionally use keyword NEST before field driverInfo, to make the default behavior of nesting more explicit. 

CREATE JSON RELATIONAL DUALITY VIEW driver_dv AS
  SELECT JSON {'_id'        : d.driver_id,
               'driverInfo' : {'name'   : d.name,
                               'points' : d.points},
               UNNEST (SELECT JSON {...}),
               'race'        : ...}
    FROM driver d;

You cannot nest primary-key fields of the root table. In this case, that means field _id.

Example 2-8 Creating Duality View RACE_DV, With Nested Driver Information Using SQL

This example creates a duality view where the objects that are the elements of array result look like this — they contain a field driverInfo whose value is a nested object with fields driverId and name: 

{"driverRaceMapId" : 3,
 "position" : 1,
 "driverInfo" : {"driverId" : 103, "name" : "Charles Leclerc"}}
CREATE JSON RELATIONAL DUALITY VIEW race_dv AS
  SELECT JSON {'_id'   : r.race_id,
               'name'   : r.name,
               'laps'   : r.laps WITH NOUPDATE,
               'date'   : r.race_date,
               'podium' : r.podium WITH NOCHECK,
               'result' :
                 [ SELECT JSON {'driverRaceMapId' : drm.driver_race_map_id,
                                'position'        : drm.position,
                                'driverInfo'      :
                                  (SELECT JSON {'driverId' : d.driver_id,
                                                'name'     : d.name}
                                     FROM driver d WITH NOINSERT UPDATE NODELETE
                                     WHERE d.driver_id = drm.driver_id)}
                     FROM driver_race_map drm WITH INSERT UPDATE DELETE
                     WHERE drm.race_id = r.race_id ]}
    FROM race r WITH INSERT UPDATE DELETE;

Example 2-9 Creating Duality View RACE_DV, With Unnested Driver Information Using SQL

This example creates a duality view where the objects that are the elements of array result look like this — they don't contain a field driverInfo whose value is a nested object with fields driverId and name: 

{"driverId" : 103, "name" : "Charles Leclerc", "position" : 1}

Instead of using 'driverInfo' : to define top-level field driverInfo with an object value resulting from the subquery of table driver, the view definition precedes that subquery with keyword UNNEST. In all other respects, this view definition is identical to that of Example 2-8.

(The view created is the same as that created using GraphQL in Example 2-12.) 

CREATE JSON RELATIONAL DUALITY VIEW race_dv AS
  SELECT JSON {'_id'   : r.race_id,
               'name'   : r.name,
               'laps'   : r.laps WITH NOUPDATE,
               'date'   : r.race_date,
               'podium' : r.podium WITH NOCHECK,
               'result' :
                 [ SELECT JSON {'driverRaceMapId' : drm.driver_race_map_id,
                                'position'        : drm.position,
                                UNNEST
                                  (SELECT JSON {'driverId' : d.driver_id,
                                                'name'     : d.name}
                                     FROM driver d WITH NOINSERT UPDATE NODELETE
                                     WHERE d.driver_id = drm.driver_id)}
                     FROM driver_race_map drm WITH INSERT UPDATE DELETE
                     WHERE drm.race_id = r.race_id ]}
    FROM race r WITH INSERT UPDATE DELETE;

See Also:

CREATE JSON RELATIONAL DUALITY VIEW in Oracle Database SQL Language Reference

Parent topic: Car-Racing Example, Duality Views

2.4.2 Creating Car-Racing Duality Views Using GraphQL

Team, driver, and race duality views for the car-racing application are created using GraphQL.

GraphQL is an open-source, general query and data-manipulation language that can be used with various databases. A subset of GraphQL syntax and operations are supported by Oracle Database for creating JSON-relational duality views. GraphQL Language Used for JSON-Relational Duality Views describes the supported subset of GraphQL. It introduces syntax and features that are not covered here.

GraphQL queries and type definitions are expressed as a GraphQL document. The GraphQL examples shown here, for creating the car-racing duality views, are similar to the SQL examples. The most obvious difference is just syntactic.

The more important differences are that with a GraphQL definition of a duality view you don't need to explicitly specify these things:

  • Nested scalar subqueries.

  • Table links between foreign-key columns and primary-key (or unique-key) columns, as long as a child table has only one foreign key to its parent table.Foot 3

  • The use of SQL/JSON generation functions (or their equivalent syntax abbreviations).

This information is instead all inferred from the graph/dependency relations that are inherent in the overall duality-view definitions. The tables underlying a duality view form a directed dependency graph by virtue of the relations among their primary (or unique) keys and foreign keys. A foreign key from one table, T-child, to another table, T-parent, results in a graph edge (an arrow) directed from node T-child to node T-parent.

You don't need to construct the dependency graph determined by a set of tables; that's done automatically (implicitly) when you define a duality view. But it can sometimes help to visualize it.

An edge (arrow) of the graph links a table with a foreign key to the table whose primary key is the target of that foreign key. For example, an arrow from node (table) driver to node (table) team indicates that a foreign key of table driver is linked to a primary key of table team. In Figure 2-3, the arrows are labeled with the foreign and primary keys.

Figure 2-3 Car-Racing Example, Table-Dependency Graph

Description of Figure 2-3 follows
Description of "Figure 2-3 Car-Racing Example, Table-Dependency Graph"

The GraphQL code that defines a JSON-relational duality view takes the form of a GraphQL query (without the surrounding query { … } code), which specifies the graph structure, based on the dependency graph, which is used by the view. A GraphQL duality-view definition specifies, for each underlying table, the columns that are used to generate the JSON fields in the supported JSON documents.

In GraphQL, a view-defining query is represented by a GraphQL object schema, which, like the dependency graph on which it's based, is constructed automatically (implicitly). You never need to construct or see either the dependency graph or the GraphQL object schema that's used to create a duality view, but it can help to know something about each of them.

A GraphQL object schema is a set of GraphQL object types, which for a duality-view definition are based on the tables underlying the view.

The GraphQL query syntax for creating a duality view reflects the structure of the table-dependency graph, and it's based closely on the object-schema syntax. (One difference is that the names used are compatible with SQL.)

In an object schema, and thus in the query syntax, each GraphQL object type (mapped from a table) is named by a GraphQL field (not to be confused with a field in a JSON object). And each GraphQL field can optionally have an alias.

A GraphQL query describes a graph, where each node specifies a type. The syntax for a node in the graph is a (GraphQL) field name followed by its object type. If the field has an alias then that, followed by a colon (:), precedes the field name. An object type is represented by braces ({ ... }) enclosing a subgraph. A field need not be followed by an object type, in which case it is scalar.

The syntax of GraphQL is different from that of SQL. In particular, the syntax of names (identifiers) is different. In a GraphQL duality-view definition, any table and column names that are not allowed directly as GraphQL names are mapped to names that are. But simple, all-ASCII alphanumeric table and column names, such as those of the car-racing example, can be used directly in the GraphQL definition of a duality view.

For example:

  • driverId : driver_id

    Field driver_id preceded by alias driverId . 

  • driver : driver {driverId : driver_id,
                 name     : name,
                 points   : points}

    Field driver preceded by alias driver and followed by an object type that has field driver_id, with alias driverId, and fields name and points, each with an alias named the same as the field. 

  • driver {driverId : driver_id,
        name,
        points}

    Equivalent to the previous example. Aliases that don't differ from their corresponding field names can be omitted.

    In the object type that corresponds to a table, each column of the table is mapped to a scalar GraphQL field with the same name as the column.

Note:

In each of those examples, alias driverId would be replaced by alias _id, if used as a document-identifier field, that is, if driver is the root table and driver_id is its primary-key column.

Note:

In GraphQL commas (,) are not syntactically or semantically significant; they're optional, and are ignored. For readability, in this documentation we use commas within GraphQL {…}, to better suggest the corresponding JSON objects in the supported documents.

In a GraphQL definition of a duality view there's no real distinction between a node that contributes a single object to a generated JSON document and a node that contributes an array of such objects. You can use just { … } to specify that the node is a GraphQL object type, but that doesn't imply that only a single JSON object results from it in the supported JSON documents.

However, to have a GraphQL duality-view definition more closely reflect the JSON documents that the view is designed to support, you can optionally enclose a node that contributes an array of objects in brackets ([, ]).

For example, you can write [{…},…] instead of just {…},…, to show that this part of a definition produces an array of driver objects. This convention is followed in this documentation.

Keep in mind that this is only for the sake of human readers the code; the brackets are optional, where they make sense. But if you happen to use them where they don't make sense then a syntax error is raised, to help you see your mistake.

You use the root table of a duality view as the GraphQL root field of the view definition. For example, for the duality view that defines team documents, you start with table team as the root: you write team {…}.

Within the { … } following a type name (such as team), which for a duality view definition is a table name, you specify the columns from that table that are used to create the generated JSON fields.

You thus use column names as GraphQL field names. By default, these also name the JSON fields you want generated.

If the name of the JSON field you want is the not same as that of the column (GraphQL field) that provides its value, you precede the column name with the name of the JSON field you want, separating the two by a colon (:). That is, you use a GraphQL alias to specify the desired JSON field name.

For example, driverId : driver_id means generate JSON field driverId from the data in column driver_id. In GraphQL terms, driverId is an alias for (GraphQL) field driver_id.

  • Using driver_id alone means generate JSON field driver_id from the column with that name.

  • Using driverId : driver_id means generate JSON field driverId from the data in column driver_id. In GraphQL terms, driverId is an alias for the GraphQL field driver_id.

When constructing a GraphQL query to create a duality view, you add a GraphQL field for each column in the table-dependency graph that you want to support a JSON field.

In addition, for each table T used in the duality view definition:

  • For each foreign-key link from T to a parent table T-parent, you add a field named T-parent to the query, to allow navigation from T to T-parent. This link implements a one-to-one relationship: there is a single parent T-parent.

  • For each foreign-key link from a table T-child to T, you add a field named T-child to the query, to allow navigation from T to T-child. This link implements a one-to-many relationship: there can be multiple children of type T-child.

Unnesting (flattening) of intermediate objects is the same as for a SQL definition of a duality view, but instead of SQL keyword UNNEST you use GraphQL directive @unnest. (All of the GraphQL duality-view definitions shown here use @unnest.)

In GraphQL you can introduce an end-of-line comment with the hash/number-sign character, #: it and the characters following it on the same line are commented out.

Example 2-10 Creating Duality View TEAM_DV Using GraphQL

This example creates a duality view supporting JSON documents where the team objects look like this — they contain a field driver whose value is an array of nested objects that specify the drivers on the team: 

{"_id" : 301, "name" : "Red Bull", "points" : 0, "driver" : [...]}

(The view created is the same as that created using SQL in Example 2-5.) 

CREATE JSON RELATIONAL DUALITY VIEW team_dv AS
  team @insert @update @delete
    {_id    : team_id,
     name   : name,
     points : points,
     driver : driver @insert @update
       [ {driverId : driver_id,
          name     : name,
          points   : points @nocheck} ]};

Example 2-11 Creating Duality View DRIVER_DV Using GraphQL

This example creates a duality view supporting JSON documents where the driver objects look like this — they don't contain a field teamInfo whose value is a nested object with fields teamId and name. Instead, the data from table team is incorporated at the top level, with the team name as field team. 


{"_id"      : 101,
 "name"     : "Max Verstappen",
 "points"   : 0,
 "teamId"   : 103,
 "team"     : "Red Bull",
 "race"     : [...]}

Two versions of the view creation are shown here. For simplicity, a first version has no annotations declaring updatability or ETAG-calculation exclusion. 

CREATE JSON RELATIONAL DUALITY VIEW driver_dv AS
  driver
    {_id       : driver_id,
     name      : name,
     points    : points,
     team @unnest
       {teamId : team_id,
        name   : name},
     race      : driver_race_map
                   [ {driverRaceMapId : driver_race_map_id,
                      race @unnest
                        {raceId       : race_id,
                         name         : name},
                      finalPosition   : position} ]};

The second version of the view creation has updatability and ETAG @nocheck annotations. (It creates the same view as that created using SQL in Example 2-7.) 

CREATE JSON RELATIONAL DUALITY VIEW driver_dv AS
  driver @insert @update @delete
    {_id       : driver_id,
     name      : name,
     points    : points,
     team @noinsert @noupdate @nodelete
       @unnest
       {teamId : team_id,
        name   : name @nocheck},
     race      : driver_race_map @insert @update @nodelete
                   [ {driverRaceMapId : driver_race_map_id,
                      race @noinsert @noupdate @nodelete
                        @unnest
                        {raceId : race_id,
                         name   : name},
                      finalPosition   : position} ]};

Example 2-12 Creating Duality View RACE_DV Using GraphQL

This example creates a duality view supporting JSON documents where the objects that are the elements of array result look like this — they don't contain a field driverInfo whose value is a nested object with fields driverId and name: 

{"driverId" : 103, "name" : "Charles Leclerc", "position" : 1}

Two versions of the view creation are shown here. For simplicity, a first version has no annotations declaring updatability or ETAG-calculation exclusion. 

CREATE JSON RELATIONAL DUALITY VIEW race_dv AS
  race
    {_id    : race_id,
     name   : name,
     laps   : laps,
     date   : race_date,
     podium : podium,
     result : driver_race_map
       [ {driverRaceMapId : driver_race_map_id,
          position        : position,
          driver
            @unnest
            {driverId : driver_id,
             name     : name}} ]};

The second version of the view creation has updatability and ETAG @nocheck annotations. (It creates the same view as that created using SQL in Example 2-9.) 

CREATE JSON RELATIONAL DUALITY VIEW race_dv AS
  race @insert @update @delete
    {_id    : race_id,
     name   : name,
     laps   : laps @noupdate,
     date   : race_date,
     podium : podium @nocheck,
     result : driver_race_map @insert @update @delete
       [ {driverRaceMapId : driver_race_map_id,
          position        : position,
          driver @noinsert @update @nodelete
            @unnest
            {driverId : driver_id,
             name     : name}} ]};

Related Topics

See Also:

Parent topic: Car-Racing Example, Duality Views

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

As one use case, you can create multiple duality views whose supported JSON documents contain different data, depending on values in discriminating table columns.

For example, using the same underlying table, ORDERS, of purchase orders you could define duality views open_orders and shipped_orders, with the first view selecting rows with clause WHERE order_status="open" from the table and the second view selecting rows with WHERE order_status="shipped".

But note that columns used in the test of a WHERE clause in a duality view need not be used to populate any fields of the supported JSON documents. For example, the selected purchase-order documents for views open_orders and shipped_orders need not have any fields that use values of column order_status.

Each WHERE clause used in a duality-view definition must contain the keywords WITH CHECK OPTION. This prohibits any changes to the table that would produce rows that are not included by the WHERE clause test. See CREATE VIEW in Oracle Database SQL Language Reference.

The WHERE clauses you can use in duality-view definitions must be relatively simple — only the following constructs can be used:

  • Direct comparison of column values with values of other columns of the same underlying table, or with literal values. For example, height > width, height > 3.14. Only ANSI SQL comparison operators are allowed: =, <>, <, <=, >, >=.

  • A (non-aggregation) SQL expression using a column value, or a Boolean combination of such expressions. For example, upper(department) = 'SALES', salary < 100 and bonus < 15.

  • Use of SQL JSON constructs: functions and conditions such as json_value and json_exists, as well as simple dot-notation SQL syntax.

In particular, a WHERE clause in a duality-view definition cannot contain the following (otherwise, an error is raised).

  • Use of a PL/SQL subprogram.

  • Comparison with the result of a subquery. For example, t.salary > (SELECT max_sal FROM max_sal_table WHERE jobcode=t.job).

  • Reference to a column in an outer query block.

  • Use of a bind variable. For example, salary = :var1.

  • Use of an aggregation operator. For example, sum(salary) < 100.

  • Use of multiple-column operations. For example, salary + bonus < 10000.

  • Use of OR between a join condition and another test, in a subquery. Such use would make the join condition optional. For example, e.deptno=d.deptno OR e.job='MANAGER' — in this case, e.deptno=d.deptno is the join condition. (However, OR can be used this way in the top-level/outermost query.)

Example 2-13 WHERE Clause Use in Duality View Definition (SQL)

This example defines duality view race_dv_medal, which is similar to view race_dv (Example 2-9). It differs in that (1) it uses an additional WHERE-clause test to limit field result to the first three race positions (first, second, and third place) and (2) it includes only races more recent than 2019. 

CREATE JSON RELATIONAL DUALITY VIEW race_dv_medal AS
  SELECT JSON {'_id'    : r.race_id,
               'name'   : r.name,
               'laps'   : r.laps WITH NOUPDATE,
               'date'   : r.race_date,
               'podium' : r.podium WITH NOCHECK,
               'result' :
                 [ SELECT JSON {'driverRaceMapId' : drm.driver_race_map_id,
                                'position'        : drm.position,
                                UNNEST
                                  (SELECT JSON {'driverId' : d.driver_id,
                                                'name'     : d.name}
                                     FROM driver d WITH NOINSERT UPDATE NODELETE
                                     WHERE d.driver_id = drm.driver_id)}
                     FROM driver_race_map drm WITH INSERT UPDATE DELETE
                     WHERE drm.race_id = r.race_id
                           AND drm.position <= 3 WITH CHECK OPTION ]}
    FROM race r WITH INSERT UPDATE DELETE
    WHERE r.race_date >= to_date('01-JAN-2020') WITH CHECK OPTION;

Example 2-14 WHERE Clause Use in Duality View Definition (GraphQL)

This example defines duality view race_dv_medal using GraphQL. It is equivalent to creating the view using SQL as in Example 2-13.

The view is similar to view race_dv (Example 2-12). It differs in that (1) it uses an additional WHERE-clause test to limit field result to the first three race positions (first, second, and third place) and (2) it includes only races more recent than 2019. 

CREATE JSON RELATIONAL DUALITY VIEW race_dv_medal AS
  race @insert @update @delete
       @where (sql: "race_date >= to_date('01-JAN-2020')")
  {_id    : race_id,
   name   : name,
   laps   : laps @noupdate,
   date   : race_date,
   podium : podium @nocheck,
   result : driver_race_map @insert @update @delete
                            @where (sql: "position <= 3")
     {driverRaceMapId : driver_race_map_id,
      position        : position,
      driver @noupdate @nodelete @noinsert
        @unnest
        {driverId : driver_id,
         name     : name}}};

Parent topic: Car-Racing Example, Duality Views


Footnote Legend

Footnote 1: In the notation used here, N does not represent a number; it's simply an abbreviation for "many", or more precisely, "one or more".
Footnote 2: In the notation used here, N does not represent a number; it's simply an abbreviation for "many", or more precisely, "one or more".
Footnote 3: The only time you need to explicitly use a foreign-key link in GraphQL is when there is more than one foreign-key relation between two tables or when a table has a foreign key that references the same table. In such a case, you use an @link directive to specify the link. See Oracle GraphQL Directives for JSON-Relational Duality Views.