How to update jsonb sub element postgres?

Question

Table name: the_table Column name: the_column

I want to update from this: old_json: {"aa": {"bb": {"asd": "asd", "qqq": "aqaq", "the_key": "the_value"}"}} modified json: {"aa": {"bb": {"asd": "asd", "qqq": "aqaq", "the_key": "the_NEW_value"}"}}

In my case I have to update many similar rows like above. if I do like that update the_table set the_column = jsonb_set(the_column, '{aa}', '"the_value"') then result be like : {"aa": "the_value"}

Then I tried update the_table set the_column = jsonb_set(the_column, '{aa: {bb: {the_key}}}', '"the_value"') but it's does't work

So how to update jsonb correctly?

S-Man · Accepted Answer · 2021-01-06 13:48:12Z

1

demo:db<>fiddle

The second parameter of jsonb_set() is an text array which contains the keys as path to your values:

UPDATE the_table 
SET the_column = jsonb_set(the_column, '{aa, bb, the_key}', '"the_NEW_value"');

answered Jan 6, 2021 at 13:48

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

How to update jsonb sub element 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