Обсуждение: unique index on embedded json object
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)
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
Thanks,
Jason
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
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