Updating jsonb field using concatenation operator in postgres

Question

I am trying to update a jsonb field which has nested json objects in it using the concatenation operator in postgres as in dbfidde

But, it removes all other fields for example if I run the below script

UPDATE t 
SET details = details || '{"name": {"firstname": "newname"},"address":{"city":"newCity"}}'
WHERE details -> 'name' ->>'firstname'='myname'

It removes the "lastname" field from the updated field

I tried updating with jsonb_set too but I am not sure how I can update multiple properties using jsonb_set

Can you explain what you are trying to achieve? As far as I understand:old JSON {"a":"b"} extend with {"a":"c"}. But than you have twice the same key... — S-Man
– S-Man, Commented Sep 13, 2019 at 17:27
Your dbfiddle seems to show exactly what you're asking for. Are you saying that when you do it on a real DB that the behavior is different? — J Spratt
– J Spratt, Commented Sep 13, 2019 at 17:56
@JSpratt The query in the question removes the "lastname" property as in dbfiddle.uk/… — DoIt
– DoIt, Commented Sep 13, 2019 at 18:29
@S-Man I am trying to update the firstname property in name object and city property in address object in the same query — DoIt
– DoIt, Commented Sep 13, 2019 at 18:30

S-Man · Accepted Answer · 2019-09-13 21:46:38Z

3

The || way does not work because the new name value is {"firstname":"newname"}. This replaces the complete old value {"firstname": "myname","lastname":"last"} and so removes the lastname attribute. This way only works if you are working on the toplevel fields. But you want to update the nested objects.

In that case I see no way around two separate calls of jsonb_set(). One possible way is to nest them:

demo:db<>fiddle

UPDATE t
SET details = 
        jsonb_set(
            jsonb_set(details, '{name,firstname}','"newname"'), 
            '{address,city}', 
            '"newCity"'
        )
WHERE details -> 'name' ->> 'firstname' = 'myname';

Updating the original details field; setting the new firstname value through the given path '{name,firstname}'
The resulting JSON object can be used directly for a following update of the city value using the same way.

answered Sep 13, 2019 at 21:46

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

7 Comments

DoIt Over a year ago

I am still figuring out a way, if I have to update like 5 or even more nested objects with the above query. Do you have anything on your mind?

S-Man Over a year ago

Maybe you can post a fiddle with your real data? I could have a look

DoIt Over a year ago

What if I have to update firstname, last name, homeaddress and workaddress in a single query. Here is the fiddle dbfiddle.uk/…

S-Man Over a year ago

the addresses are json objects. Would you like to replace the whole value or only replace the city element?

S-Man Over a year ago

dbfiddle.uk/… The name object can be aggregated in this special case because there are no further subelements. So the whole name object can be replaced in one throw. Since both cities have different paths you'll need two different calls.

|

Collectives™ on Stack Overflow

Updating jsonb field using concatenation operator in postgres

1 Answer 1

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Related