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?
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;
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
uptade table_name set col = col || '{"b": 10, "c": 20}'::jsonb where id = ?
Well, my example is not about merging 2 json fields, but arrays instead:
insert into tag_lists (article_id, tags) values (1, '{job}')
on conflict (article_id)
do update set tags = (
select array_agg(distinct x) from unnest(tag_lists.tags || excluded.tags) x
);
Thanks to this answer for providing comprehensive snippets
