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. Tablesteam
,driver
, andrace
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:
-
Working with JSON Relational Duality Views using SQL, a SQL script that mirrors the examples in this document
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 columnteam_id
of tableteam
, which is the root table underlying duality viewteam_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, fielddriverId
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. Columnteam_id
of tableteam
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 fieldsname
andpoints
. -
The team information that's shared with driver documents is in fields
driverId
,name
, andpoints
, under fielddriver
. The value of fielddriverId
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. Columndriver_id
of thedriver
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
, andpoints
. -
The driver information that's shared with race documents is in field
race
. The value of fieldraceId
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. Columnrace_id
of therace
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
, andposition
. -
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
(underwinner
,firstRunnerUp
, andsecondRunnerUp
, which are underpodium
).
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
andpoints
. -
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
, andpodium
. -
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
andpoints
.
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:
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 follows](img/car-racing_directed_entity-relationship.png)
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
NULL
able unique key or a unique key that has someNULL
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
NULL
able, 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 columnteam_id
of tableteam
-
-
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 follows](img/car-racing_directed_entity-relationship-w-mapping_entity.png)
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 columnrace_id
of tablerace
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 columndriver_id
of tabledriver
-
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.
See Also:
-
JSON Schema in Oracle Database JSON Developer’s Guide
-
CREATE TABLE in Oracle Database SQL Language Reference
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 annotatedCHECK
, 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-byteRAW
column holds the ETAG value for the current row of columnDATA
. That is, it holds the data used for the document metadata fieldetag
. -
RESID
— This variable-lengthRAW
column holds an object identifier that uniquely identifies the document that is the content of the current row of columnDATA
. 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.
- 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 inWHERE
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.
Related Topics
See Also:
-
CREATE JSON RELATIONAL DUALITY VIEW in Oracle Database SQL Language Reference
-
Generation of JSON Data Using SQL in Oracle Database JSON Developer’s Guide for information about SQL/JSON functions
json_object
,json_array
, andjson_arrayagg
, and the syntaxJSON {
…}
andJSON [
…]
-
JSON Data Type Constructor in Oracle Database JSON Developer’s Guide
-
System Change Numbers (SCNs) in Oracle Database Concepts
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 follows](img/car-racing-tables-graph.png)
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 aliasdriverId
. -
driver : driver {driverId : driver_id, name : name, points : points}
Field
driver
preceded by aliasdriver
and followed by an object type that has fielddriver_id
, with aliasdriverId
, and fieldsname
andpoints
, 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 fielddriver_id
from the column with that name. -
Using
driverId : driver_id
means generate JSON fielddriverId
from the data in columndriver_id
. In GraphQL terms,driverId
is an alias for the GraphQL fielddriver_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:
-
CREATE JSON RELATIONAL DUALITY VIEW in Oracle Database SQL Language Reference
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
andjson_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.