unique index on embedded json object

[Lee Jason]

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

[John R Pierce]

*
*
Any suggestions, please? Thank you.

store your structured data in conventional SQL tables.

[Peter Geoghegan]

Post by Lee Jason
{"id": "12345",
"bags": [{
"sku": "abc123",
"price": 0,
},
{
"sku": "abc123",
"price": 0,
}]
}

That's invalid JSON - there are stray commas.

Post by Lee Jason
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.

Unique indexes are useful for ensuring that each row is unique based
on some criteria, possibly a quite complex expression. However, it's
not clear what behavior is expected here, since multiple SKUs may
appear per row. If you had a table with "products", with a jsonb
column, and one row per product, you could then usefully extract at
most one SKU per row, and that could work fine. But the structure you
present isn't amenable to that.