14.9.1.4 Modifying JSON Using a Duality View

Modify related table data by inserting, updating, or deleting JSON documents through a duality view.

Since you include the @insert, @update, and @delete annotations when you define the duality view, you can use the view to modify action items and team members, too. For example, you can insert a new action item with its related action team members in a single INSERT statement like this:

declare
    l_json json := 
           json(q'~
            {
              "name": "Buy Talulah's Retirement Gift",
              "team": [
                {
                  "role": "LEAD",
                  "name": "Abigail"
                },
                {
                  "role": "MEMBER",
                  "name": "Chaz"
                }
              ]
            }
       ~');
begin
    insert into action_items_dv(data)
    values (l_json)
    returning data into l_json;
    dbms_output.put_line(json_serialize(l_json pretty));
end;

This single INSERT statement into the ACTION_ITEMS_DV JSON Relational Duality View handles:

• Looking up the staff_id for each team member since name is unique in the STAFF table,
• Inserting the new action item into the ACTION_ITEMS table, and
• Inserting two new action item team members into the ACTION_ITEM_TEAM_MEMBERS table.

It returns the JSON document below:

{
  "_id" : 58,
  "name" : "Buy Talulah's Retirement Gift",
  "status": "OPEN",
  "team" :
  [
    {
      "team_assignment_id" : 127,
      "role" : "MEMBER",
      "staff_id" : 23,
      "name" : "Chaz"
    },
    {
      "team_assignment_id" : 128,
      "role" : "LEAD",
      "staff_id" : 18,
      "name" : "Abigail"
    }
  ],
  "_metadata" :
  {
    "etag" : "48A8D3D393B6653A5D2B0359EDAD2D21",
    "asof" : "000025C52A6F11EE"
  }
}