Oracle Supported GraphQL Directives for JSON-Relational Duality Views
GraphQL directives are annotations that specify additional information or particular behavior for a GraphQL schema. All of the Oracle GraphQL directives for defining duality views apply to GraphQL fields.
A GraphQL directive is a name with prefix @,
followed in some cases by arguments.
Oracle GraphQL for defining duality views provides the following directives:
-
Directive
@arrayforces the object type that immediately follows it to correspond to a JSON array. For example, this code produces an array of one or more objects with fieldsmyIdandaField.@array {myId : my_id, aField : a_column}@arrayand enclosing the object in[ ]have the same effect. This means that even if only a single object is produced, it's enclosed in an array.Here, for example, even if table
driver_tabhas only one matching row, the value of fielddriverswill be[{"driverId":…, "name":…, "points":…}].drivers : driver_tab [ {driverId : driver_id, name : name, points : points} ]};Note:
You cannot use directive
@arrayat the top level of a document. The JSON value that corresponds to the root table of a duality view must always be an object. -
Directive
@castproduces a binary JSON-language value that can serve as a document identifier. For this you must use argumentaswith valueid. A typical use case is for a field in one kind of document to refer to a particular document of another kind, by using its document identifier as the field value.For example, an employee document can include the employee's department number as field
deptNo, whose value is a JSON binary identifier. This ensures that the underlyingRAWvalue of columndept_columnis suitable to identify a specific department document (by its field_id).deptNo : dept_column @cast(as:id) -
Directives
@[no]checkdetermine which duality-view parts contribute to optimistic concurrency control. They correspond to SQL annotation keywords [NO]CHECK, which are described in described in Creating Car-Racing Duality Views Using GraphQL. -
Directives
@[no]deleteserve as duality-view updating annotations. They correspond to SQL annotation keywords [NO]DELETE, which are described in Annotations (NO)UPDATE, (NO)INSERT, (NO)DELETE, To Allow/Disallow Updating Operations. -
Directive
@flexdesignates aJSON-type column as being a flex column for the duality view. Use of this directive is covered in Flex Columns, Beyond the Basics. -
Directives
@nestand@unnestspecify nesting and unnesting (flattening) of intermediate objects in a duality-view definition. Directive@unnestcorresponds to SQL keywordUNNEST(there's no keywordNESTin SQL corresponding to directive@nest).Restrictions (an error is raised if not respected):
-
You cannot nest fields that correspond to identifying columns of the root table (primary-key columns, identity columns, or columns with a unique constraint or unique index).
-
You cannot unnest a field that has an alias.
Example 9-1 illustrates the use of
@nest. See Creating Car-Racing Duality Views Using GraphQL for examples that use@unnest. -
-
Directive
@generatedspecifies a JSON field or object that's generated. This augments the documents supported by a duality view. Their fields are not mapped to individual underlying columns, and are thus read-only.Directive
@generatedtakes optional argumentpathorsql, with an value that's used to calculate the JSON field value. The path value is a SQL/JSON path expression. Thesqlvalue is a SQL expression or query. See Generated Fields, Hidden Fields. -
Directive
@hiddenspecifies a JSON field that's hidden; it is not present in any document supported by the duality view. Directive@hiddentakes no arguments. See Generated Fields, Hidden Fields. -
Directives
@[no]insertserve as duality-view updating annotations. They correspond to SQL annotation keywords [NO]INSERT, which are described in Annotations (NO)UPDATE, (NO)INSERT, (NO)DELETE, To Allow/Disallow Updating Operations. -
Directive
@linkdisambiguates multiple foreign-key links between columns. See Oracle GraphQL Directive @link. The@linkcan also be used to explicitly define the joining columns, ensuring that the system does not depend on referential constraints. -
Directive
@objectforces the object-type node that immediately follows it to correspond to a single JSON object, not an array of objects. For example, this code ensures that the value of fielddriveris a driver object, not an array of such objects.driver : driver @insert @update @object {driverId : driver_id, name : name, points : points @nocheck} -
Directives
@[no]updateserve as duality-view updating annotations. They correspond to SQL annotation keywords [NO]UPDATE, which are described in Annotations (NO)UPDATE, (NO)INSERT, (NO)DELETE, To Allow/Disallow Updating Operations.
Example 9-1 Creating Duality View DRIVER_DV1, With Nested Driver Information
This example creates duality view driver_dv1,
which is the same as view driver_dv defined with GraphQL in Example 3-10 and defined with SQL in Example 3-3, except that fields name and
points from columns of table driver are nested
in a subobject that's the value of field driverInfo.Foot 1 The
specification of field driverInfo is the only difference between
the definition of view driver_dv1 and that of the original view,
driver_dv.
The corresponding GraphQL and SQL definitions of
driver_dv1 are shown.
CREATE JSON RELATIONAL DUALITY VIEW driver_dv1 AS
driver
{_id : driver_id,
driverInfo : driver @nest {team : 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} ]};Here is the corresponding SQL definition:
CREATE JSON RELATIONAL DUALITY VIEW driver_dv1 AS
SELECT JSON {'_id' : d.driver_id,
'driverInfo' : {'name' : d.name,
'points' : d.points},
UNNEST
(SELECT JSON {'teamId' : t.team_id,
'team' : t.name}
FROM team t
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
WHERE r.race_id = drm.race_id),
'finalPosition' : drm.position}
FROM driver_race_map drm
WHERE drm.driver_id = d.driver_id ]}
FROM driver d;Table driver is the root table of the view, so its
fields are all unnested in the view by default, requiring the use of
@nest in GraphQL to nest them.
(Fields from non-root tables are nested by default, requiring the
explicit use of @unnest (keyword UNNEST in SQL) to
unnest them. This is the case for team fields teamId and
name as well as race fields raceId and
name.)
_________________________________________________________
Related Topics
Oracle GraphQL Directive @link
GraphQL directive @link disambiguates multiple
foreign-key links between columns in tables underlying a duality view.
Directive @link specifies a link, or join, between columns
of the tables underlying a duality view. Usually the columns are for different tables,
but columns of the same table can also be linked, in which case the foreign key is said
to be self-referencing.
The fact that in general you need not explicitly specify foreign-key links is an advantage that GraphQL presents over SQL for duality-view definition — it's less verbose, as such links are generally inferred by the underlying table-dependency graph.
The only time you need to explicitly use a foreign-key link in GraphQL is
when either (1) there is more than one foreign-key relation between two tables or
(2) a table has a foreign key that references the same table, or both. In such a
case, you use an @link directive to specify a particular
link: the foreign key and the link direction.
@link can accept both arguments from and
to at the same time. In such cases, the @link
directive requires a single argument, named to or
from, which specifies, for a duality-view field whose
value is a nested object, whether to use (1) a foreign key of the table whose columns
define the nested object's fields — the to direction or (2) a foreign key
of the table whose columns define the nesting/enclosing object's fields —
the from direction.
to or from argument is a
GraphQL list of strings, where each string names a single foreign-key column (for
example, to : ["FKCOL"]). A GraphQL list of more than one string
represents a compound foreign key, for example, to : ["FKCOL1",
"FKCOL2"]). (A GraphQL list corresponds to a JSON array. Commas are
optional in GraphQL.)
Note:
Additionally, if you want to explicitly specify which columns to use for joining the two tables, you can do so by listing the joining columns of the outer table in the from argument of@link and those of the
inner table in the to argument of @link.
@link Directive to Identify Different Foreign-Key Relations Between Tables
The first use case for @link directives, disambiguating multiple
foreign-key relations between different tables, is illustrated by duality views
team_dv2 and driver_dv2.
The team_w_lead table definition in Example 9-2 has a foreign-key link from column lead_driver
to driver table column driver_id. And the
driver table definition there has a foreign-key link from its
column team_id to the team_w_lead table's
primary-key column, team_id.
The table-dependency graph in Figure 9-1 shows these two dependencies. It's the same as the graph in Figure 3-1, except that it includes the added link from table
team_w_lead's foreign-key column lead_driver
to primary-key column driver_id of table
driver.
The corresponding team duality-view definitions are in Example 9-3 and Example 9-4.
Figure 9-1 Car-Racing Example With Team Leader, Table-Dependency Graph
Description of "Figure 9-1 Car-Racing Example With Team Leader, Table-Dependency Graph"
Example 9-2 Creating Table TEAM_W_LEAD With LEAD_DRIVER Column
This example creates table team_w_lead, which is
the same as table team in Example 2-4, except that it has the additional column
lead_driver, which is a foreign key to column
driver_id of table driver.
CREATE TABLE team_w_lead
(team_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
name VARCHAR2(255) NOT NULL UNIQUE,
lead_driver INTEGER,
points INTEGER NOT NULL,
CONSTRAINT team_pk PRIMARY KEY(team_id),
CONSTRAINT lead_fk FOREIGN KEY(lead_driver) REFERENCES driver(driver_id));Table driver, in turn, has foreign-key column
team_id, which references column team_id of
the team table. For the examples here, we assume that table driver
has the same definition as in Example 2-4, except that its foreign key refers to table
team_w_lead, not to the table team of Example 2-4. In other words, we use this driver table
definition here:
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_w_lead(team_id));Because there are two foreign-key links between tables
team_w_lead and driver, the team and driver
duality views that make use of these tables need to use directive
@link, as shown in Example 9-3 and Example 9-4.
Example 9-3 Creating Duality View TEAM_DV2 With LEAD_DRIVER, Showing GraphQL Directive @link
This example is similar to Example 3-6, but it uses table team_w_lead, defined in Example 9-2, which has foreign-key column lead_driver.
Because there are two foreign-key relations between tables
team_w_lead and driver it's necessary to use
directive @link to specify which foreign key is used where.
The value of top-level JSON field leadDriver is
a driver object provided by foreign-key column lead_driver
of table team_w_lead. The value of top-level field
driver is a JSON array of driver objects provided by
foreign-key column team_id of table
driver.
The @link argument for field
leadDriver uses from because its value,
lead_driver, is the foreign-key column in table
team_w_lead, which underlies the outer/nesting object.
This is a one-to-one join.
The @link argument for field driver
uses to because its value, team_id, is the
foreign-key column in table driverFoot 2, which underlies the inner/nested object. This is
a one-to-many join.
CREATE JSON RELATIONAL DUALITY VIEW team_dv2 AS
team_w_lead
{_id : team_id,
name : name,
points : points,
leadDriver : driver @link (from : ["LEAD_DRIVER"])
{driverId : driver_id,
name : name,
points : points},
driver : driver @link (to : ["TEAM_ID"])
[ {driverId : driver_id,
name : name,
points : points} ]};Example 9-4 Creating Duality View DRIVER_DV2, Showing GraphQL Directive @link
This example is similar to Example 3-10, but it uses table team_w_lead, defined in Example 9-2, which has foreign-key column lead_driver.
Because there are two foreign-key relations between tables
team_w_lead and driverFoot 2 it's necessary to use directive @link
to specify which foreign key is used where.
The @link argument for field team uses
from because its value, team_id, is
the foreign-key column in table driver, which underlies the
outer/nesting object.
CREATE JSON RELATIONAL DUALITY VIEW driver_dv2 AS
driver
{_id : driver_id
name : name
points : points
team_w_lead
@link (from: ["TEAM_ID"])
@unnest
{teamId : team_id,
team : name}
race : driver_race_map
[ {driverRaceMapId : driver_race_map_id,
race @unnest
{raceId : race_id,
name : name}
finalPosition : position} ]};@link Directive to Identify a Foreign-Key Relation That References the Same Table
The second use case for @link directives, identifying a
self-referencing foreign key, from a given table to itself, is illustrated by
duality views team_dv3, driver_dv3, and
driver_manager_dv.Foot 3
The driver_w_mgr table definition in Example 9-5 has a foreign-key link from column manager_id to
column driver_id of the same table,
driver_w_mgr.Foot 4
The table-dependency graph in Figure 9-2 shows this self-referential table dependency. It's a simplified version of the
graph in Figure 3-1 (no race table or driver_race
map mapping table), but it includes the added link from table
driver_w_mgr's foreign-key column manager_id
to primary-key column driver_id of the same table.
Figure 9-2 Car-Racing Example With Driver Self-Reference, Table-Dependency Graph
Description of "Figure 9-2 Car-Racing Example With Driver Self-Reference, Table-Dependency Graph"
The team_dv3 and driver_dv3
duality-view definitions are in Example 9-6 and Example 9-7, respectively. Concerning the use of @link, the
salient differences from the original car-racing views, team_dv and
driver_dv, are these:
-
The information in array
driverof viewteam_dv3identifies each driver's manager, in fieldmanagerId. -
View
driver_dv3includes the identifier of the driver's manager, in fieldboss.
The third duality view here, driver_manager_dv
contains information for the manager as a driver (fields name and
points), and it includes information for the drivers who report
to the manager (array reports). Its definition is in Example 9-8.
Example 9-5 Creating Table DRIVER_W_MGR With Column MANAGER_ID
This example creates table driver_w_mgr, which is
the same as table driver in Example 2-4, except that it has the additional column
manager_id, which is a foreign key to column
driver_id of the same table
(driver_w_mgr).
CREATE TABLE driver_w_mgr
(driver_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
name VARCHAR2(255) NOT NULL UNIQUE,
points INTEGER NOT NULL,
team_id INTEGER,
manager_id INTEGER,
CONSTRAINT driver_pk PRIMARY KEY(driver_id),
CONSTRAINT driver_fk1 FOREIGN KEY(manager_id) REFERENCES driver_w_mgr(driver_id),
CONSTRAINT driver_fk2 FOREIGN KEY(team_id) REFERENCES team(team_id));
Because foreign-key column manager_id references the
same table, driver_w_mgr, the driver duality view
(driver_dv3) and the manager duality view
(driver_manager_dv) that make use of this table need to use
directive @link, as shown in Example 9-7 and Example 9-8, respectively.
Example 9-6 Creating Duality View TEAM_DV3 (Drivers with Managers)
The definition of duality view team_dv3 is the same as that
of duality view team_dv in Example 3-6, except that it uses table driver_w_mgr
instead of table driver, and the driver information in array
driver includes field managerId, whose
value is the identifier of the driver's manager (from column
manager_id of table
driver_w_mgr).
CREATE JSON RELATIONAL DUALITY VIEW team_dv3 AS
team @insert @update @delete
{_id : team_id,
name : name,
points : points,
driver : driver_w_mgr @insert @update
[ {driverId : driver_id,
name : name,
managerId : manager_id,
points : points @nocheck} ]};
This is the equivalent SQL definition of the view:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW team_dv3 AS
SELECT JSON {'_id' : t.team_id,
'name' : t.name,
'points' : t.points,
'driver' :
[ SELECT JSON {'driverId' : d.driver_id,
'name' : d.name,
'managerId' : d.manager_id,
'points' : d.points WITH NOCHECK}
FROM driver_w_mgr d WITH INSERT UPDATE
WHERE d.team_id = t.team_id ]}
FROM team t WITH INSERT UPDATE DELETE;Three team documents are inserted into view team_dv3. Each driver
object in array driver has a managerId field,
whose value is either the identifier of the driver's manager or
null, which indicates that the driver has no manager (the
driver is a manager). In this use case all drivers on a team have the same manager
(who is also on the team).
INSERT INTO team_dv3 VALUES ('{"_id" : 301,
"name" : "Red Bull",
"points" : 0,
"driver" : [ {"driverId" : 101,
"name" : "Max Verstappen",
"managerId" : null,
"points" : 0},
{"driverId" : 102,
"name" : "Sergio Perez",
"managerId" : 101,
"points" : 0} ]}');
INSERT INTO team_dv3 VALUES ('{"_id" : 302,
"name" : "Ferrari",
"points" : 0,
"driver" : [ {"driverId" : 103,
"name" : "Charles Leclerc",
"managerId" : null,
"points" : 0},
{"driverId" : 104,
"name" : "Carlos Sainz Jr",
"managerId" : 103,
"points" : 0} ]}');
INSERT INTO team_dv3 VALUES ('{"_id" : 303,
"name" : "Mercedes",
"points" : 0,
"driver" : [ {"driverId" : 105,
"name" : "George Russell",
"managerId" : null,
"points" : 0},
{"driverId" : 106,
"name" : "Lewis Hamilton",
"managerId" : 105,
"points" : 0},
{"driverId" : 107,
"name" : "Liam Lawson",
"managerId" : 105,
"points" : 0} ]}');Example 9-7 Creating Duality View DRIVER_DV3 (Drivers with Managers)
This example is a simplified version of the view defined in Example 3-10. It includes neither the team nor the race information for a
driver. Instead it includes the identifier of the driver's manager, in field
boss.
It uses table driver_w_mgr, defined in Example 9-5, to obtain that manager information using foreign-key column
manager_id. Because that foreign-key relation references the
same table, driver_w_mgr, it's necessary to use
directive @link to specify the foreign key.
The @link argument for field
boss uses from because its
value, ["MANAGER_ID"], names the foreign-key column in table
driver_w_mgr, which underlies the
outer/nesting object. This is a one-to-one join.
CREATE JSON RELATIONAL DUALITY VIEW driver_dv3 AS
driver_w_mgr @insert @update @delete
{_id : driver_id,
name : name,
points : points @nocheck,
boss : driver_w_mgr @link (from : ["MANAGER_ID"])
{driverId : driver_id,
name : name}};
This is the equivalent SQL definition of the view, which makes the join explicit:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW driver_dv3 AS
SELECT JSON {'_id' : d1.driver_id,
'name' : d1.name,
'points' : d1.points WITH NOCHECK,
'boss' : (SELECT JSON {'driverId' : d2.driver_id,
'name' : d2.name,
'points' : d2.points WITH NOCHECK}
FROM driver_w_mgr d2
WHERE d1.manager_id = d2.driver_id)}
FROM driver_w_mgr d1 WITH INSERT UPDATE DELETE;
This query selects the document for driver 106 (Lewis Hamilton):
SELECT json_serialize(DATA PRETTY)
FROM driver_dv3 v WHERE v.data."_id" = 106;It shows that the driver, Lewis Hamilton, has manager George Russell. The driver-to-boss relation is one-to-one.
JSON_SERIALIZE(DATAPRETTY)
--------------------------
{
"_id" : 106,
"_metadata" :
{
"etag" : "998443C3E7762F0EB88CB90899E3ECD1",
"asof" : "0000000000000000"
},
"name" : "Lewis Hamilton",
"points" : 0,
"boss" :
{
"driverId" : 105,
"name" : "George Russell",
"points" : 0
}
}
1 row selected.
Example 9-8 Creating Duality View DRIVER_MANAGER_DV
This duality view provides information about a driver who manages other
drivers. Fields _id, name, and
points contain information about the manager. Field
reports is an array of the drivers reporting to the manager:
their IDs, names and points.
The @link argument for field
reports uses to because its
value, ["MANAGER_ID"], names the foreign-key column in table
driver_manager_dv, which underlies the
inner/nested object. This is a one-to-many join.
CREATE JSON RELATIONAL DUALITY VIEW driver_manager_dv AS
driver_w_mgr @insert @update @delete
{_id : driver_id,
name : name,
points : points @nocheck,
reports : driver_w_mgr @link (to : ["MANAGER_ID"])
[ {driverId : driver_id,
name : name,
points : points @nocheck} ]};
This is the equivalent SQL definition of the view, which makes the join explicit:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW driver_manager_dv AS
SELECT JSON {'_id' : d1.driver_id,
'name' : d1.name,
'points' : d1.points WITH NOCHECK,
'reports' : [ SELECT JSON {'driverId' : d2.driver_id,
'name' : d2.name,
'points' : d2.points WITH NOCHECK}
FROM driver_w_mgr d2
WHERE d1.driver_id = d2.manager_id ]}
FROM driver_w_mgr d1 WITH INSERT UPDATE DELETE;
This query selects the document for driver (manager) 105 (George Russell):
SELECT json_serialize(DATA PRETTY)
FROM driver_manager_dv v WHERE v.data."_id" = 105;It shows that the manager, George Russell, has two drivers reporting to him, Lewis Hamilton and Liam Lawson. The manager-to-reports relation is one-to-many.
JSON_SERIALIZE(DATAPRETTY)
--------------------------
{
"_id" : 105,
"_metadata" :
{
"etag" : "7D91177F7213E086ADD149C2193182FD",
"asof" : "0000000000000000"
},
"name" : "George Russell",
"points" : 0,
"reports" :
[
{
"driverId" : 106,
"name" : "Lewis Hamilton",
"points" : 0
},
{
"driverId" : 107,
"name" : "Liam Lawson",
"points" : 0
}
]
}
1 row selected.
Footnote Legend
Footnote 1: Updating and ETAG-checking annotations are not shown here.Footnote 2: We assume the definition of table driver given in Example 9-2.
Footnote 3: The data used here to illustrate this use case is fictional.
Footnote 4: There might not be a real-world use case for a race-car driver's manager who is also a driver. The ability to identify a foreign-key link from a table to itself is definitely useful, however.