Forum Stats

  • 3,770,131 Users
  • 2,253,073 Discussions
  • 7,875,336 Comments

Discussions

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

bsamuel
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 (@.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!

Tagged:

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.

Answers