Document-Identifier Field for Duality Views

5 Document-Identifier Field for Duality Views

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

Often there is only one such primary-key column. If there is than one then we sometimes speak of the primary key being composite.

If there is only one primary-key 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 primary-key columns.

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

Example 5-1 Document Identifier Field _id: Primary-Key Column Value

A single primary-key column, race_id, is used as the value of field _id.

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;

A document supported by the view would look like this: {"_id" : 1,…}.

Example 5-2 Document Identifier Field _id: Object Value

The field value is an object with a single member, which maps the single primary-key column, race_id, to a meaningful field name, raceId.

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;

A document supported by the view would look like this: {"_id" : {"raceId" : 1},...}.

An alternative car-racing design might instead use a race table that has multiple primary key columns, say race_id and date:

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;

In that case, a document supported by the view would look like this: {"_id" : {"raceId" : 1, "date" : "2022-03-20T00:00:00"},...}.

Related Topics

Car-Racing Example, JSON Documents