Do UPSERT based on specific value of JSON in Postgres 10

Question

I have a Postgres table messages as follows:

Column   |           Type             | Collation | Nullable |                    
-----------+--------------------------+-----------+----------
 id        | integer                  |           | not null |
 message   | jsonb                    |           |          | 
 date      | timestamp with time zone |           | not null | 

id | message                      | date
1  | {"name":"alpha", "pos":"x"}  | 2020-02-11 12:31:44.658667+00
2  | {"name":"bravo", "pos":"y"}  | 2020-02-11 12:32:43.123678+00
3  | {"name":"charlie", "pos":"z"}| 2020-02-11 12:38:37.623535+00

What I would like to do is do an UPSERT based on the value of the name key i.e., if there is an insert with same name value, then the other value pos is updated, otherwise a new entry is created.

I did CREATE UNIQUE INDEX message_name ON messages((message->>'name'));

I found the INSERT ON CONFLICT in Postgres 9.5+ but I can't understand how to use the unique index with this.

I don't know if this is the correct approach to do it in the first place so if there is a better way to do this, I would appreciate the input.

Answer 1

You need to repeat the expression from the index:

insert into messages (message)
values ('{"name":"alpha", "pos":"new pos"}')
on conflict ((message->>'name')) 
  do update 
     set message = jsonb_set(messages.message, '{pos}'::text[], excluded.message -> 'pos', true)
;

If you have more keys in the JSON and want to replace (or add) all of them, you can use this:

insert into messages (message)
values ('{"name":"alpha", "pos":"new pos", "some key": 42}')
on conflict ((message->>'name')) 
  do update 
     set message = messages.message || (excluded.message - 'name')
;