Creating Car-Racing Duality Views Using SQL

3.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 9-1 for an example. Note that you cannot nest the document identifier field, _id, which corresponds to the identifying columns of the root table; an error is raised if you try.

Example 3-1 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 3-6.)

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 3-2 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 3-3 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 3-2.

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

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 fields (other than _id) from the root table, driver, to be in a nested object, you could do that. For example, this code 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;

Example 3-4 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 3-5 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 3-4.

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

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;