0

Postgresql 10+

Example from the documentation...

jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false)

results in...

[{"f1":[2,3,4],"f2":null},2,null,3]

Fair enough. But I need to find my target node by attribute value, not index. For the life of me, I cannot figure out how do something like...

jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{(where f1 = 1),f1}','[2,3,4]', false)

Any advice on how to accomplish this? Thanks!

Jim Ott
  • 725
  • 13
  • 24
  • 1
    [postgresql 9.5 using jsonb_set for updating specific jsonb array value](https://stackoverflow.com/q/38996212/1995738) – klin Aug 22 '18 at 14:38

1 Answers1

2

You can split the steps into two jobs:

  1. Split in elements (jsonb_arral_elements)
  2. Indentify wich elements must change (case when...)
  3. Update that element (jsonb_set)
  4. Join all together (jsonb_agg)

solution

select jsonb_agg(case when element->>'f1'='1' then jsonb_set(element, '{f1}', '[2,3,4]') else element end)
  from jsonb_array_elements('[{"f1":1,"f2":null},2,null,3,{"f1":3},{"f1":1,"f2":2}]'::jsonb) element

note

I changed the input adding two more elements with "f1" key

Emilio Platzer
  • 2,327
  • 21
  • 29
  • Thank you for your response. That does indeed change the json, however, I don't see that it provides a means to update the record. Maybe it's my fault for not making it clear that that's what I needed. Correct me if I'm wrong, but it looks like to me like I *must* get the item index in order to change the item while keeping the whole node. The link posted by @klin works. Kind of verbose and convoluted, but so far, the only thing that works for me. – Jim Ott Aug 22 '18 at 16:01
  • @JimOtt, you can use it in an update statement. See at: http://rextester.com/BLC52070 – Emilio Platzer Aug 22 '18 at 17:58