CODEWITHSUNDEEP
jsonpostgresql
pktCoder
pktCoder

Reputation: 1115

insert JSON document while maintaining uniqueness of a field

Trying to update/insert a JSON record in postgresql (9.5.4) with the following table:

create table (detail jsonb);

insert into table VALUES ('{"name": "tom", "zip": 88717, "city": "dallas"}');

Suppose I get from UI a JSON {"name": "tom", "zip": 78717, "city": "houston"} that I want to update/insert while maintaining the uniqueness of name field. Tried the following but gets error

ERROR:  syntax error at or near "->"
LINE 2: on conflict (detail->'name')

Here is the SQL statement that caused the error:

insert into jsonTest VALUES ('{"name": "tom", "zip": 78717, "city": "houston"}') 
on conflict (detail->'name') 
do update set  detail = '{"name": "tom", "zip": 78717, "city": "houston"}'::jsonb where  detail->>'name' == 'tom';

Upvotes: 0

Views: 53

Answers (1)

J. Alderete
J. Alderete

Reputation: 116

Assuming you created the UNIQUE index on detail->>'name', your insert should look like this:

insert into jsonTest VALUES ('{"name": "tom", "zip": 78717, "city": "houston"}') 
on conflict ((detail->>'name'))
do update set  detail = '{"name": "tom", "zip": 78717, "city": "houston"}'::jsonb

I just added another parenthesis to the on conflict ((detail->>'name')) and removed the where clause.

EDIT: To create an unique index on detail->>'name' you can do the following:

CREATE UNIQUE INDEX jsonTest_name_index ON jsonTest((detail->>'name')) ;

After the index is created, you can use the "INSERT ON CONFLICT" without problems.

Upvotes: 1

Related Questions