postgres jsonb_set multiple keys update

The documentation says:

The || operator concatenates the elements at the top level of each of its operands. … For example, if both operands are objects with a common key field name, the value of the field in the result will just be the value from the right hand operand.

So using your example data:

update table_name set
    data = data || '{"city": "ottawa", "phone": "phonenum", "prefix": "prefixedName"}'
    where number = 1;

Additionally if the object you want to edit is not at the top level – just combine the concatenation and jsonb_set function. For example, if the original data looks like

{"location": {"name": "firstName", "city": "toronto", "province": "ON"}}

then

...
data = jsonb_set(
    data, 
    '{location}', data->'location' || '{"city": "ottawa", "phone": "phonenum", "prefix": "prefixedName"}')
...

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)