8 GraphQL Language Used for JSON-Relational Duality Views

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.

This chapter describes this supported subset of GraphQL. It introduces syntax and features that are not covered in Creating Car-Racing Duality Views Using GraphQL, which presents some simple examples of creating duality views using GraphQL.

The Oracle syntax supported for creating duality views with GraphQL is a proper subset of GraphQL as specified in Sections B.1, B.2, and B.3 of the GraphQL specification (October 2021), except that user-supplied names must follow satisfy some Oracle-specific rules specified here.

The Oracle GraphQL syntax also provides some additional, optional features that facilitate use with JSON-relational duality views. If you need to use GraphQL programmatically, and you want to stick with the standard GraphQL syntax, you can do that. If you don't have that need then you might find the optional syntax features convenient.

For readers familiar with GraphQL, the supported subset of the language does not include these standard GraphQL constructs:

  • Mutations and subscriptions. Queries are the only supported operations.

  • Inline fragments. Only a predefined FragmentSpread syntax is supported.

  • Type definitions (types interface, union, enum, and input object, as well as type extensions). Only GraphQL Object and Scalar type definitions are supported.

  • Variable definitions.

Using GraphQL to define a duality view has some advantages over using SQL to do so. These are covered in Creating Car-Racing Duality Views Using GraphQL. In sum, the GraphQL syntax is simpler and less verbose. Having to describe the form of supported documents and their parts using explicit joins between results of JSON-generation subqueries can be a bother and error prone.

Oracle GraphQL support for duality views includes these syntax extensions and simplifications:

  1. Scalar Types

    Oracle Database supports additional GraphQL scalar types, which correspond to Oracle JSON-language scalar types and to SQL scalar types. See Oracle GraphQL Scalar Types.

  2. Implicit GraphQL Field Aliasing

    Unaliased GraphQL field names used in a duality-view definition are automatically taken as aliases to the actual GraphQL field names. In effect, this is a shorthand convenience for providing case-sensitive matching that corresponds to field names in the documents supported by the duality view. See Implicit GraphQL Field Aliasing.

  3. GraphQL Directives For Duality Views

    Oracle GraphQL provides directives (@link, @[un]nest, and @flex), which specify particular handling when defining duality views. See Oracle GraphQL Directives for JSON-Relational Duality Views.

  4. GraphQL Names in Duality-View Definitions

    If the table and column names you use in a duality-view definition are directly usable as standard GraphQL field names then they are used as is. This is the case, for instance in the car-racing duality views.

    More generally, a duality-view definition specifies a mapping between (1) JSON field names, (2) GraphQL type and field names, and (3) SQL table and column names. The first two are case-sensitive, whereas unquoted SQL names are case-insensitive. Additionally, the characters allowed in names differ between GraphQL and SQL.

    For these reasons, Oracle relaxes and extends the unquoted GraphQL names allowed in duality-view definitions.

    See Names Used in GraphQL Duality-View Definitions.

Oracle GraphQL Scalar Types

Table 8-1 lists the Oracle-supported GraphQL scalar types that correspond to Oracle JSON scalar types and to Oracle SQL scalar types. It lists both standard GraphQL types and custom, Oracle-specific GraphQL types.

Table 8-1 Scalar Types: Oracle JSON, GraphQL, and SQL

Oracle JSON-Language Scalar Type GraphQL Scalar Type SQL Scalar Type

binary

Binary (Oracle-specific)

RAW or BINARY

date

Date (Oracle-specific)

DATE

day-second interval

DaysecondInterval (Oracle-specific)

INTERVAL DAY TO SECOND

double

Float (standard GraphQL)

BINARY_DOUBLE

float

Float (standard GraphQL)

BINARY_FLOAT

timestamp

Timestamp (Oracle-specific)

TIMESTAMP

vector

Vector (Oracle-specific)

VECTOR

timestamp with time zone

TimestampWithTimezone (Oracle-specific)

TIMESTAMP WITH TIME ZONE

year-month interval

YearmonthInterval (Oracle-specific)

INTERVAL YEAR TO MONTH

Implicit GraphQL Field Aliasing

The body of a duality view definition is a GraphQL query. If a field name is used in that query with no alias then it is matched case-insensitively to pick up the corresponding GraphQL field name. In a standard GraphQL query such matching is case-sensitive.

This convenience feature essentially provides the unaliased field with an alias that has the lettercase used in the view definition. The alias corresponds directly with the JSON field name used in supported documents. The actual GraphQL field name is derived from a SQL table or column name:

For example, if a GraphQL field name is defined as myfield (lowercase), and a duality view-creation query uses myField then the queried field is implicitly treated as if it were written myField : myfield, and a JSON document supported by the view would have a JSON field named myField.

Names Used in GraphQL Duality-View Definitions

Oracle relaxes and extends the unquoted GraphQL names allowed in duality-view definitions. This is done to facilitate (1) specifying the field names of the JSON documents supported by a duality view and (2) use of SQL identifier syntax (used for tables and columns) in GraphQL names.

If none of the names you use in a GraphQL duality-view definition contain the period (dot) character, (.) or need to be quoted, then the corresponding GraphQL schema is fully compliant with the GraphQL standard. In this case, it should work with all existing GraphQL tools.

Otherwise (the more typical case), it is not fully compliant. It can be used to create a JSON-relational duality view, but it might not work correctly with some GraphQL tools.

Standard GraphQL names are restricted in these ways:

  • They can only contain alphanumerical ASCII characters and underscore (_) characters.

  • They cannot start with two underscore characters: __.

SQL names, if quoted, can contain any characters except double-quote (") (also called quotation mark, code point 34) and null (code point 0). Unquoted SQL names can contain alphanumeric characters (ASCII or not), underscores (_), number signs (#), and dollar signs ($). A fully qualified table name contains a period (dot) character (.), separating the database schema (user) name from the table name.

The following rules apply to GraphQL names allowed in duality-view definitions. The last of these rules applies to fully qualified SQL table names, that is, to names of the form <schema name>.<table name>, which is composed of three parts: a database schema (user) name, a period (dot) character (.), and a database table name. The other rules apply to SQL names that don't contain a dot.

  • The GraphQL name that corresponds to a quoted SQL name (identifier) is the same quoted name.

    For example, "this name" is the same for SQL and GraphQL.

  • The GraphQL name that corresponds to an unquoted SQL name that is composed of only ASCII alphanumeric or underscore (_) characters is the same as the SQL name, except that:

    • A GraphQL field name is lowercase.

      For example, GraphQL field name MY_NAME corresponds to SQL name my_name.

    • A GraphQL type name is capitalized.

      For example, GraphQL type name My_name corresponds to SQL name MY_NAME.

  • The GraphQL name that corresponds to an unquoted SQL name that has one or more non-ASCII alphanumeric characters, number sign (#) characters, or dollar sign ($) characters is the same name, but uppercased and quoted. (In Oracle SQL, such a name is treated case-insensitively, whether quoted or not.)

    For example, GraphQL name "MY#NAME$4"corresponds to SQL name my#name$4

  • The GraphQL name that corresponds to a fully qualified SQL table name, which has the form <schema name>.<table name>, is the concatenation of (1) the GraphQL name corresponding to <schema name>, (2) the period (dot) character (.), and (3) the GraphQL name corresponding to <table name>. Note that the dot is not quoted in the GraphQL name.

    Examples for fully qualified SQL names:

    • GraphQL name My_schema.Mytable corresponds to SQL name MY_SCHEMA.MYTABLE.

    • GraphQL name "mySchema".Mytable corresponds to SQL name "mySchema".mytable.

    • GraphQL name "mySchema"."my table" corresponds to SQL name "mySchema"."my table".

    • GraphQL name "Schema#3.Table$4" corresponds to SQL name SCHEMA#3.TABLE$4.

See Also:

Graph QL

8.1 Oracle 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 directive is a name with prefix @, followed in some cases by arguments.

Oracle GraphQL for defining duality views provides the following directives:

  • Directive @flex designates a JSON-type column as being a flex column for the duality view. Use of this directive is covered in Flex Columns: Duality-View Schema Flexibility and Evolution.

  • Directives @nest and @unnest specify nesting and unnesting (flattening) of intermediate objects in a duality-view definition. They correspond to SQL keywords NEST and UNNEST, respectively.

    By default, fields corresponding to root-table columns are unnested and those corresponding to non-root-table columns are nested. Note that you cannot nest fields that correspond to primary-key columns of the root table; an error is raised if you try.

    Example 8-1 illustrates the use of @nest. See Creating Car-Racing Duality Views Using GraphQL for examples that use @unnest.

  • Directive @link disambiguates multiple foreign-key links between columns. See Oracle GraphQL Directive @link.

  • Directives @[no]update, @[no]insert, and @[no]delete serve as duality-view updating annotations. They correspond to SQL annotation keywords [NO]UPDATE, [NO]INSERT, and [NO]DELETE, which are described in Annotations (NO)UPDATE, (NO)INSERT, (NO)DELETE, To Allow/Disallow Updating Operations.

  • Directives @[no]check determine 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.

Example 8-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 2-11 and defined with SQL in Example 2-7, 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 {name   : name,
                                points : points},
     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} ]};

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

8.1.1 Oracle GraphQL Directive @link

GraphQL directive @link disambiguates multiple foreign-key links between columns.

Directive @link specifies a link between a foreign-key column and a primary-key or unique-key column, in 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 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 a particular link: the foreign key and direction.

The team_w_lead table definition in Example 8-2 has a foreign-key link from column lead_driver to driver table column driver_id. And the driver table 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 8-1 shows these two dependencies. It's the same as the graph in Figure 2-3, 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 8-3 and Example 8-4.

Figure 8-1 Car-Racing Example With Team Leader, Table-Dependency Graph

Description of Figure 8-1 follows
Description of "Figure 8-1 Car-Racing Example With Team Leader, Table-Dependency Graph"

An @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.

The value of a 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.)

Example 8-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 table team_w_lead. 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 8-3 and Example 8-4.

Example 8-3 Creating Duality Views TEAM_DV2 With LEAD_DRIVER, Showing GraphQL Directive @link

This example is similar to Example 2-10, but it uses table team_w_lead, defined in Example 8-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.

The @link argument for field driver uses to because its value, team_id, is the foreign-key column in table driver, which underlies the inner/nested object.

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 8-4 Creating Duality View DRIVER_DV2, Showing GraphQL Directive @link

This example is similar to Example 2-11, but it uses table team_w_lead, defined in Example 8-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 @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      : team_w_lead
       @link (from: ["team_id"])
       @unnest
       {teamId : team_id,
        name   : name}
     race      : driver_race_map
                   [ {driverRaceMapId : driver_race_map_id,
                      race @unnest
                        {raceId       : race_id,
                         name         : name}
                      finalPosition   : position} ]};


Footnote Legend

Footnote 1: Updating and ETAG-checking annotations are not shown here.