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 you want an "upsert", you can do this with insert ... on conflict...

insert into the_table (id, json_column)
values (1, '{"b": 10, "c": 20}'::jsonb)
on conflict (id) do update
   set json_column = table_name.json_column || excluded.json_column;