Jsonb_set: How to Update ALL array elements with key

Question

I have a table 'things' with a set of columns

id | name | data
1  | 'hi' | [{name: 'what', amount: 10}, {name:'koo', amount: 15}, {name: 'boo', amount: 13}]

I want to change the amount to 0, in all of the array elements. I.e, I want the result to be

[{name: 'what', amount: 0}, {name:'koo', amount:0}, {name: 'boo', amount: 0}]

When I do this

UPDATE      things
SET         data = jsonb_set(data, '{0,amount}', '0', false)
WHERE       id=1

This works, but only sets the first array element amount to 0. I.e the result is

[{name: 'what', amount: 0}, {name:'koo', amount: 15}, {name: 'boo', amount: 13}]

I want them all to be 0.

How do I do that?

E_net4 · Accepted Answer · 2019-06-11 18:54:34Z

2

You can split it apart, update it, and put it back together again:

select id, 
       name, 
       jsonb_agg(jsonb_set(array_elems, '{amount}', '0')) -- update each element of the array and aggregate them back together
FROM things
JOIN LATERAL (
    select jsonb_array_elements(data) -- split the array into each element
) sub(array_elems) ON TRUE
GROUP BY id, name;
 id | name |                                          jsonb_agg
----+------+---------------------------------------------------------------------------------------------
  1 | hi   | [{"name": "what", "amount": 0}, {"name": "koo", "amount": 0}, {"name": "boo", "amount": 0}]

Here is the step of updating the table.

WITH updated_data AS (
  select id, 
       jsonb_agg(jsonb_set(array_elems, '{amount}', '0')) as updated
  FROM things
  JOIN LATERAL (
    select jsonb_array_elements(data) -- split the array into each element
  ) sub(array_elems) ON TRUE
  GROUP BY id
)
UPDATE things set data = updated
FROM updated_data 
WHERE things.id = updated_data.id;

edited Jun 11, 2019 at 18:54

E_net4

30.6k13 gold badges118 silver badges155 bronze badges

answered Jun 11, 2019 at 14:39

Jeremy

6,77322 silver badges21 bronze badges

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

1 Comment

barsju Over a year ago

Awsome answer, but I think this can be made even simpler using cross join: cross join jsonb_array_elements(mp.id_numbers) array_elems

Collectives™ on Stack Overflow

Jsonb_set: How to Update ALL array elements with key

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related