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"}')...
You can try this
Here we are using jsonb
concatation operator ||
to Concatenate two jsonb objects
update table_name set data = (select val from ((select CASE WHEN data ? key THEN jsonb_set(data, '{' || key || '}', quote_nullable(updated_value))ELSE data || ('{' || quote_ident(key) || ':' || quote_ident(some_value) || '}')::jsonbEND val from json_each_text((select data::json from tbl))CROSS JOIN tbl twhere key in ('city','phone','prefix') and number=1)) where number=1