How can I merge an existing jsonb field when upserting a record in Postgres?

Question

Say I have a table on Postgres with a jsonb column containing {"a": 1, "b": 2}. Now I'd like to upsert a record with the same id and {"b": 10, "c": 20} as the jsonb column value.

Consequently, I'd like the jsonb field of the row to contain {"a": 1, "b": 10, "c": 20}. How can this be achieved?

Answer 1

If concatenate 2 jsonb value, you achieve what you want, for example:

select '{"a": 1, "b": 2}'::jsonb  || '{"b": 10, "c": 20}'::jsonb 

produces: "{"a": 1, "b": 10, "c": 20}"

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.

https://www.postgresql.org/docs/current/static/functions-json.html