Re: unique index on embedded json object

On Sun, Sep 21, 2014 at 5:23 AM, Lee Jason <jawc@hotmail.com> wrote:
> {"id": "12345",
> "bags": [{
>     "sku": "abc123",
>     "price": 0,
> },
> {
>     "sku": "abc123",
>     "price": 0,
> }]
> }

That's invalid JSON - there are stray commas.

> 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.

--
Regards,
Peter Geoghegan