14.9.4.3 Processing and Accessing JSON Easily

Process nested JSON values directly with JSON_TABLE and JSON_VALUE.

Using a cursor for loop, you can process nested team members using a combination of JSON_TABLE and JSON_VALUE on the JSON-type payload without reparsing the document each time.

-- Ensure role values are legal
for k in (select team_member 
            from json_table(p_action_item,'$.team[*]' 
                 columns (team_member json path '$')))
loop
    app_common.report_if_error(
        action_items_common.is_valid_role(
            json_value(k.team_member,'$.role')));
end loop;