Update selected values in a jsonb column containing a array

Question

Table faults contains column recacc (jsonb) which contains an array of json objects. Each of them contains a field action. If the value for action is abc, I want to change it to cba. Changes to be applied to all rows.

[
  {
    "action": "abc",
    "created": 1128154425441
  },
  {
    "action": "lmn",
    "created": 1228154425441
  },
  {
    "action": "xyz",
    "created": 1328154425441
  }
]

The following doesn't work, probably because of the data being in array format

update faults 
  set recacc = jsonb_set(recacc,'{action}', to_jsonb('cbe'::TEXT),false) 
where recacc ->> 'action' = 'abc'

Kaushik Nayak Kaushik Nayak · Accepted Answer · 2019-01-08T12:54:17

I'm not sure if this is the best option, but you may first get the elements of jsonb using jsonb_array_elements, replace it and then reconstruct the json using array_agg and array_to_json.

UPDATE faults SET recacc = new_recacc::jsonb
FROM 
 (SELECT array_to_json(array_agg(s)) as new_recacc 
 FROM
   (  SELECT 
         replace(c->>'action','abc','cba') , --this to change the value
          c->>'created' FROM faults f
       cross join lateral jsonb_array_elements(f.recacc)  as c
   )  as s (action,created)
) m;

Demo

Update selected values in a jsonb column containing a array

1 Answers