14.8.2.1 Constructing or Querying an Action Item

Compare programmatic and SQL-based ways to build action item JSON.

For educational purposes, the GET_OBJECT procedure shows two different ways to return the JSON representation of an action item. As shown below, if the c_get_object_approach constant equals c_approach_programmatic then it calls GET_OBJECT_PROGRAMMATICALLY. If the constant is c_approach_sql instead, then it calls GET_OBJECT_USING_SQL.

-- in package action_items_api
function get_object(
    p_id in number)
return json_object_t
is
    c_get_object_approach constant t_get_object_approach := c_approach_sql;
    l_ret json_object_t;
begin
    case c_get_object_approach
        when c_approach_programmatic then
            l_ret := get_object_programmatically(p_id);
        when c_approach_sql then
            l_ret := json_object_t(get_object_using_sql(p_id));
    end case;
    return l_ret;
end;

GET_OBJECT_PROGRAMMATICALLY shown below selects the data from ACTION_ITEMS and ACTION_ITEMS_TEAM_MEMBERS tables into appropriate PL/SQL record structures. Then it uses the PL/SQL JSON_OBJECT_T and JSON_ARRAY_T types to create the action item JSON property by property. Notice that the code first builds up the contents of the JSON array, and then adds the array to the action item JSON object with a property name of team at the end.

-- in package action_items_api
function get_object_programmatically(
    p_id in number)
return json_object_t
is
    l_action_item_rec action_items%rowtype;
    l_action_item_obj json_object_t := json_object_t();
    l_team_members_arr json_array_t := json_array_t();
    l_team_members     t_action_item_team_members := t_action_item_team_members();
    l_team_member_obj  json_object_t;
    l_team_member_name staff.name%type;
begin
    app_common.report_if_error(
        action_items_common.ensure_action_exists(p_id));    
    select *
      into l_action_item_rec
      from action_items
     where id = p_id;
    l_action_item_obj.put('_id',l_action_item_rec.id);
    l_action_item_obj.put('name',l_action_item_rec.name);
    l_action_item_obj.put('status',l_action_item_rec.status);
    select *
      bulk collect into l_team_members
      from action_item_team_members
     where action_id = p_id;
    for j in 1..l_team_members.count loop
        l_team_member_obj := json_object_t();
        l_team_member_obj.put('team_assignment_id',l_team_members(j).id);
        l_team_member_obj.put('role',l_team_members(j).role);
        l_team_member_obj.put('staff_id',l_team_members(j).user_id);
        -- Lookup staff name using id
        select name
          into l_team_member_name
          from staff
         where id = l_team_members(j).user_id;
        l_team_member_obj.put('name',l_team_member_name);
        -- Append team member object to the team array
        l_team_members_arr.append(l_team_member_obj);
    end loop;
    l_action_item_obj.put('team',l_team_members_arr);
    return l_action_item_obj;
end get_object_programmatically;

As shown below, GET_OBJECT_USING_SQL uses the JSON_OBJECT and JSON_ARRAYAGG functions to create the action item JSON object with its nested team members. Notice the RETURNING CLOB clauses on the array function and on the outer use of JSON_OBJECT to ensure the JSON construction is not limited to 4000 characters.

-- in package action_items_api   
function get_object_using_sql(
    p_id in number)
return clob
is
    l_action_item_json_clob clob;
begin
    app_common.report_if_error(
        action_items_common.ensure_action_exists(p_id));
    select json_object(
      '_id' value ai.id,
      'name' value ai.name,
      'status' value ai.status,
      'team' value (
        select json_arrayagg(
          json_object(
            'team_assignment_id' value tm.id,
            'role' value tm.role,
            'staff_id' value tm.user_id,
            'name' value s.name
          )
        returning clob /* json_arrayagg */)
        from action_item_team_members tm
        left join staff s on tm.user_id = s.id
        where tm.action_id = ai.id
      )
    returning clob /* json_object */)
    into l_action_item_json_clob
    from action_items ai
    where ai.id = p_id;
    return l_action_item_json_clob;   
end get_object_using_sql;