14.9.1.2 Defining JSON Relational Duality View

Define a JSON relational duality view that maps related table data into a single JSON document.

The following is the simple JSON Relational Duality View data definition language (DDL) statement for a JSON view of action items, their team members, and related staff names. Notice the ACTION_ITEMS table forms the "root" of JSON view, the team property automatically includes the data from the ACTION_ITEMS_TEAM_MEMBERS in an array, and it pulls in the name of the action team member from the STAFF table. The @insert, @update, and @delete annotations on a table indicate which DML operations are allowed. If no annotation is included on a table, as for STAFF below, then its data is read-only in the duality view. You don't need to express how the tables are related, because the duality view infers that from foreign key constraints. The @unnest annotation promotes the staff_id and name properties to be at the same level as team_assignment_id and role.

create json relational duality view action_items_dv as 
action_items @insert @update @delete 
{
  _id: id,
  name: name,
  status: status,
  team: action_item_team_members @insert @update @delete
  {
    team_assignment_id: id,
    role: role,
    staff @unnest
    {
      staff_id: id,
      name: name
    }
  }
};