2

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"
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
jojo
  • 395
  • 3
  • 14

1 Answers1

2

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • thanks for helping me out understand. Data is not as regular as the sample data. Can you help me handle for this data in here https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=cc93fb9e3abbdf47fd7f0d1d35878b78. I get null value for first object in the array . Insted i should have as it is from original array if the desired path ie. '{ao,sc}' is not null – jojo Jan 20 '21 at 16:10
  • Link to fiddle to retrieve the desired result. Can someone take a look? https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=2ccf9ef6827a4ee24603c791119f46e6 – jojo Jan 21 '21 at 08:09
  • 1
    @jojo: Please start a new *question* for your new question with everything necessary to understand it. You can always link to this one for context, and drop a forward link here (to also get my attention). – Erwin Brandstetter Jan 21 '21 at 16:46
  • i have asked a new question. https://stackoverflow.com/questions/65817635/conditional-update-with-jsonb-set?noredirect=1#comment116376569_65817635 – jojo Jan 21 '21 at 17:02