CODEWITHSUNDEEP
databasepostgresqljsonb
Arkhatinho
Arkhatinho

Reputation: 21

How to update jsonb sub element postgres?

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?

Upvotes: 1

Views: 178

Answers (1)

S-Man
S-Man

Reputation: 23716

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"');

Upvotes: 1

Related Questions