CODEWITHSUNDEEP
sqljsonpostgresql-9.5
Wrishi
Wrishi

Reputation: 33

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

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]

Upvotes: 1

Views: 1068

Answers (1)

user330315
user330315

Reputation:

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"}]'

Upvotes: 3

Related Questions