Forum Stats

  • 3,770,131 Users
  • 2,253,073 Discussions


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

bsamuel Member Posts: 24 Blue Ribbon

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 ( == "&PRD_NAME.") however it doesn't work if moving it into a database package as you can't build the comparison, eg ( == "' || p_prd_name || '") and the "passing" option is invalid in the "remove" clause.

update orders
   set order_attr = json_transform ( order_attr
                                   , remove '$.products[*]?( == "&PRD_NAME.")' )
 where json_exists(order_attr, '$.products[*]?( == $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.



Best Answer

  • User_H3J7U
    User_H3J7U Member Posts: 690 Silver Trophy
    Accepted Answer

    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.