How to use json_transform to remove an array element at an unknown index?

bsamuel

In Oracle 21c how would one go about removing an array element at an unknown index using JSON_TRANSFORM? The following works fine in Apex as the item substitution does its job when using the comparison (@.name == "&PRD_NAME.") however it doesn't work if moving it into a database package as you can't build the comparison, eg (@.name == "' || p_prd_name || '") and the "passing" option is invalid in the "remove" clause.

update orders
   set order_attr = json_transform ( order_attr
                                   , remove '$.products[*]?(@.name == "&PRD_NAME.")' )
 where json_exists(order_attr, '$.products[*]?(@.name == $s)' passing :PRD_NAME as "s");

In the example many orders may have the product in question so this update needs to work on multiple rows.

Thanks!

User_H3J7U

Passing is not working in json_transform, moreover, documentation has wrong syntax diagram (19 and 21)

As workaround, use dynamic sql for json path expression. This will lead to excessive hard parses.

User_H3J7U

Passing is not working in json_transform, moreover, documentation has wrong syntax diagram (19 and 21)

As workaround, use dynamic sql for json path expression. This will lead to excessive hard parses.

bsamuel

Thanks - so it would appear it should be able to do this using the "passing" clause but can't.