Document-Identifier Field for Duality Views

6 Document-Identifier Field for Duality Views

A document supported by a duality view always includes, at its top level, a document-identifier field, _id, which corresponds to the identifying columns (primary-key columns or columns with a unique constraint or unique index) of the root table underlying the view. The field value can take different forms.

Often there is only one such identifying column and it is often a primary-key column. If there is more than one primary-key column then we sometimes speak of the primary key being composite.

If there is only one identifying column then you use that as the value of field _id when you define the duality view.
Alternatively, you can use an object as the value of field _id. The members of the object specify fields whose values are the identifying columns. An error is raised if there is not a field for each of the identifying columns.

If there is only one identifying column, you can nevertheless use an object value for _id; doing so lets you provide a meaningful field name.

Example 6-1 Document Identifier Field _id With Primary-Key Column Value

For duality view race_dv, the value of a single primary-key column, race_id, is used as the value of field _id. A document supported by the view would look like this: {"_id" : 1,…}.

GraphQL:

CREATE JSON RELATIONAL DUALITY VIEW race_dv AS
  race {_id     : race_id
        name    : name
        laps    : laps @NOUPDATE
        date    : race_date
        podium  : podium @NOCHECK,
        result  : ...};

SQL:

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' : ...}
  FROM race;

Example 6-2 Document Identifier Field _id With Object Value

For duality view race_dv, the value of field _id is an object with a single member, which maps the single primary-key column, race_id, to a meaningful field name, raceId. A document supported by the view would look like this: {"_id" : {"raceId" : 1},...}.

GraphQL:

CREATE JSON RELATIONAL DUALITY VIEW race_dv AS
  race {_id @NEST {race_id}
        name    : name
        laps    : laps @NOUPDATE
        date    : race_date
        podium  : podium @NOCHECK,
        result  : ...};

SQL:

CREATE JSON RELATIONAL DUALITY VIEW race_dv AS
  SELECT JSON {'_id'    : {'raceId' : r.race_id},
               'name'   : r.name,
               'laps'   : r.laps WITH NOUPDATE,
               'date'   : r.race_date,
               'podium' : r.podium WITH NOCHECK,
               'result' : ...}
  FROM race;

An alternative car-racing design might instead use a race table that has multiple identifying columns, race_id and date, which together identify a row. In that case, a document supported by the view would look like this: {"_id" : {"raceId" : 1, "date" : "2022-03-20T00:00:00"},...}.

GraphQL:

CREATE JSON RELATIONAL DUALITY VIEW race_dv AS
  race {_id @NEST {raceId: race_id, date: race_date}
        name    : name
        laps    : laps @NOUPDATE
        podium  : podium @NOCHECK,
        result  : ...};

SQL:

CREATE JSON RELATIONAL DUALITY VIEW race_dv AS
  SELECT JSON {'_id'    : {'raceId' : r.race_id, 'date' : r.race_date},
               'name'   : r.name,
               'laps'   : r.laps WITH NOUPDATE,
               'podium' : r.podium WITH NOCHECK,
               'result' : ...}
  FROM race;

Related Topics

Car-Racing Example, JSON Documents