How to update a value based on key in json array in postgres?

Question

In a table there is a column data(jsonb) and json array like this

[
  {"pid": "123", "percentage": "10"}, 
  {"pid": "456", "percentage": "50"}, 
  {"pid": "789", "percentage": "40"}
]

I want to update percentage 30 where pid is 789. I used this query but not succeeded.

UPDATE table
SET data =
jsonb_set(data, '{pid}'::text[], data->'pid' || '{"percentage":"30"}'::jsonb)
WHERE (data->> 'pid') = '789' and id= '1'; [id is table's primary key]

Unrelated to your problem, but: Postgres 9.5 is no longer supported you should plan an upgrade as soon as possible. — user330315
– user330315, Commented Jul 13, 2021 at 6:40
This would be so easy with a properly normalized data model. — user330315
– user330315, Commented Jul 13, 2021 at 6:41

user330315 · Accepted Answer · 2021-07-13 06:54:15Z

3

There is no easy way to do this (except to change your data model to properly normalized model). You will have to unnest the array, and replace the percentage for the PID in question. Then aggregate the elements back into an array.

You also can't use ->> on an array as that operator doesn't work with arrays.

update the_table t
   set data = (select jsonb_agg(case d.element ->> 'pid' 
                                  when '789' then d.element || '{"percentage": 30}'
                                  else d.element 
                                end)
               from jsonb_array_elements(t.data) as d(element))
where id = 1
  and data @> '[{"pid": "789"}]'

answered Jul 13, 2021 at 6:54

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to update a value based on key in json array in postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related