Upserting a postgres jsonb based on multiple properties in jsonb field

Question

I am trying to upsert into a table with jsonb field based on multiple json properties in the jsonb field using below query

insert into testtable(data) values('{
    "key": "Key",
    "id": "350B79AD",
    "value": "Custom"
}')
On conflict(data ->>'key',data ->>'id')
do update set data =data || '{"value":"Custom"}'
WHERE data ->> 'key' ='Key' and data ->> 'appid'='350B79AD'

Above query throws error as below

ERROR:  syntax error at or near "->>"
LINE 8: On conflict(data ->>'key',data ->>'id')

am I missing something obvious here?

The conflict target has to be a unique constraint or, as a shortcut, the columns which are unique. You can't choose part of a jsonb object. — Jeremy
So. Seems like only way to do this is get the json first and update or insert? — DoIt

Barbaros Özhan Barbaros Özhan · Accepted Answer · 2019-10-08T12:08:45

I suppose you want to insert unique id and key combination value into the table. Then you need a unique constraint for them :

create unique index on testtable ( (data->>'key'), (data->>'id') );

and also use extra parentheses for the on conflict clause as tuple :

on conflict( (data->>'key'), (data->>'id') )

and qualify the jsonb column name ( data ) by table name (testtable) whenever you meet after do update set or after where clauses as testtable.data. So, convert your statement to :

insert into testtable(data) values('{
    "key": "Key",
    "id": "350B79AD",
    "value": "Custom1"
}')
    on conflict( (data->>'key'), (data->>'id') )
    do update set data = testtable.data || '{"value":"Custom2"}'
 where testtable.data ->> 'key' ='Key' and testtable.data ->> 'id'='350B79AD';

btw, data ->> 'appid'='350B79AD' converted to data ->> 'id'='350B79AD' ( appid -> id )

Demo

Upserting a postgres jsonb based on multiple properties in jsonb field

1 Answers