| From: | Lee Jason <jawc(at)hotmail(dot)com> |
|---|---|
| To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | unique index on embedded json object |
| Date: | 2014-09-21 12:23:12 |
| Message-ID: | SNT151-W958134DD9A06AB822BE1AED0B20@phx.gbl |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Hi forks,
I am testing postgresql 9.4 beta2 right now. I am wondering if it is possible to create a unique index on embedded json object?
For example,
I create a table names "products"
CREATE TABLE products (oid serial primary key, data jsonb)
Now, I try to insert json object into data column.
{"id": "12345","bags": [{ "sku": "abc123", "price": 0,},{ "sku": "abc123", "price": 0,}]}
However, I want sku of bags to be unique. It means the json can't be inserted into products tables because sku is not unique in this case.
I tried to create a unique index like below, but it failed.
CREATE UNIQUE INDEX product_sku_index ON products( (data->'bags'->'sku') )
Any suggestions, please? Thank you.
Thanks,
Jason
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Andreas Joseph Krogh | 2014-09-21 12:44:06 | Re: Strategy for moving a large DB to another machine with least possible down-time |
| Previous Message | Bill Moran | 2014-09-21 11:51:00 | Re: Strategy for moving a large DB to another machine with least possible down-time |