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
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
@JSpratt The query in the question removes the "lastname" property as in dbfiddle.uk/… — DoIt
@S-Man I am trying to update the firstname property in name object and city property in address object in the same query — DoIt

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

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.

Updating jsonb field using concatenation operator in postgres

1 Answers