Postgres Pro
Downloads
Home   >   mailing lists

Thread: unique index on embedded json object

unique index on embedded json object

From
Lee Jason
Date:
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

Re: unique index on embedded json object

From
John R Pierce
Date:
On 9/21/2014 5:23 AM, Lee Jason wrote:

Any suggestions, please?  Thank you.

store your structured data in conventional SQL tables.



-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Re: unique index on embedded json object

From
Peter Geoghegan
Date:
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