Search code examples
sqlpostgresqlsql-updateaggregate-functionsjsonb

UPDATE with jsonb_set() only affects one object in nested array

Trying to update all elements of a nested array in a jsonb column, but only one element is updated. My query:

update table_ 
 set value_ = jsonb_set(value_,cte.json_path,cte.namevalue,false) FROM (
select 
 vals2->'ao'->'sc'->'name' as namevalue,
  ('{iProps,'||index1-1||',value,rules,'||index2-1||',ao,sc}')::text[] as json_path
from 
  table_, 
  jsonb_array_elements(value_->'iProps') 
  with ordinality arr1(vals1,index1),
  jsonb_array_elements(vals1->'value'->'rules') 
  with ordinality arr2(vals2,index2)
  ) AS cte;

See demo with sample value:

db<>fiddle here

Am unable to understand why this query updates the first object in the rules array:

iProps -> value -> rules -> ao -> sc -> name = "name1"

But not the subsequent ones:

iProps -> value -> rules -> ao -> sc -> name = "name2"
iProps -> value -> rules -> ao -> sc -> name = "name3"
Solution

  • Explanation

    The subselect in the FROM clause of your UPDATE returns three rows. But every row in the target table can only be updated once in a single UPDATE command. The result is that you only see the effect of one of those three rows.

    Or, in the words of the manual:

    When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

    Aside: don't call your subquery "cte". It's not a Common Table Expression.

    Proper UPDATE

    UPDATE table_ t
SET    value_ = jsonb_set(value_, '{iProps}', sub2.new_prop, false)
FROM  (
   SELECT id
        , jsonb_agg(jsonb_set(prop, '{value, rules}', new_rules, false)
                    ORDER BY idx1) AS new_prop
   FROM  (
      SELECT t.id, arr1.prop, arr1.idx1
           , jsonb_agg(jsonb_set(rule, '{ao,sc}', rule #> '{ao,sc,name}', false)
                       ORDER BY idx2) AS new_rules
      FROM table_ t
         , jsonb_array_elements(value_->'iProps')       WITH ORDINALITY arr1(prop,idx1)
         , jsonb_array_elements(prop->'value'->'rules') WITH ORDINALITY arr2(rule,idx2)
      GROUP  BY t.id, arr1.prop, arr1.idx1
      ) sub1
   GROUP  BY id
   ) sub2
WHERE t.id = sub2.id;

    db<>fiddle here

    Use jsonb_set() on each object (array element) before aggregating them back into an array. First at the leaf level, and again on the deeper level.

    I added id as PRIMARY KEY to the table. We need some unique column to keep rows separate.

    The added ORDER BY may or may not be required. Added it to guarantee original order.

    Of course, if your data is as regular as the sample, a relational design with dedicated columns might be a simpler alternative. See