14.9.4.2 Transforming JSON Declaratively in SQL

Use JSON_TRANSFORM to add, change, or remove JSON elements declaratively in SQL.

Using JSON_TRANSFORM, in the BEFORE_SAVE procedure you can default the role property to the value MEMBER on any team array object where role is null or is missing using the following declarative operation. When needed, the function lets you perform multiple transformations in a single command to add, change, or remove JSON document elements.

-- Default role to member if not supplied
select json_transform(p_action_item,  
            set '$.team[*]?(@.role == null ||
                             !exists(@.role)).role' = 'MEMBER')
into p_action_item;