14.3.4 Creating a View for GET Handlers

Create a view that returns action item data with nested team members for GET handlers.

Start by defining a view the /actionitems template's GET handlers can use. This gives a meaningful name like ACTION_ITEMS_WITH_TEAM_JV to the query that retrieves the exact action item data you want the handler to return. Select top-level ACTION_ITEMS columns you want to include, then use a subquery with JSON_ARRAYAGG and JSON_OBJECT as shown below to include the JSON array of nested ACTION_ITEM_TEAM_MEMBERS data. Notice how the subquery joins this table with STAFF to include the staff member's name in the result. Your ORDS GET handlers can now reference this query to simplify their definition.

create view action_items_with_team_jv as
select ai.id,
       ai.name,
       ai.status,
       (select json_arrayagg(
                 json_object(
                   'team_member_id' value tm.id,
                   'name'           value s.name,
                   'user_id'        value tm.user_id,
                   'role'           value tm.role
                 )
               )
          from action_item_team_members tm
          join staff s on tm.user_id = s.id
         where tm.action_id = ai.id
       ) as team
from action_items ai