7

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?

user7807740
  • 99
  • 1
  • 6

3 Answers3

11

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;
mitelone
  • 341
  • 1
  • 10
9

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

Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
  • Thanks! How would I go about upserting the new jsonb object such that the old and new one get concatenated? – user7807740 Jun 06 '17 at 20:11
  • 3
    If you need update existing jsonb value then you can do: `uptade table_name set col = col || '{"b": 10, "c": 20}'::jsonb where id = ?` – Oto Shavadze Jun 06 '17 at 20:37
1

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

Nick Roz
  • 3,918
  • 2
  • 36
  • 57