Geek Answers Handbook

Unique index on json inline object

I'm testing Postgresql 9.4 beta2 right now. I am wondering if it is possible to create a unique index on a json inline object?

I create a table name products:

CREATE TABLE products (oid serial primary key, data jsonb)

Now I am trying to insert a json object into a data column.

{
    "id": "12345",
    "bags": [
        {
            "sku": "abc123",
            "price": 0,
        },
        {
            "sku": "abc123",
            "price": 0,
        }
    ]
}

However, I want the skubags to be unique. This means that json cannot be inserted into product tables because it is skunot unique in this case.

I tried to create a unique index as shown below, but this failed.

CREATE UNIQUE INDEX product_sku_index ON products( (data->'bags'->'sku') )

Any suggestions?

+4
source share
1 answer

UNIQUE INDEX .

CREATE UNIQUE INDEX product_sku_index ON products( (data->'bags'->'sku') )p >

, ... data->'bags'->'sku'p >
.

data->'bags'->0->>'sku'

:

data#>>'{bags,0,sku}'

.
: " , ".. . , sku ? JSON json data? sku?

UNIQUE.

, sku json- data->'bags', . sku ( PK) :

CREATE TABLE prod_sku(sku text PRIMARY KEY);  -- PK enforces uniqueness

.
Postgres:

. :

DELETE FROM hostname h
USING  unnest(OLD.hostnames) d(x)
WHERE  h.hostname = d.x;

...

INSERT INTO hostname(hostname)
SELECT h
FROM   unnest(NEW.hostnames) h;

:

DELETE FROM prod_sku p
USING  jsonb_array_elements(NEW.data->'bags') d(x)
WHERE  p.sku = d.x->>'sku';

...

INSERT INTO prod_sku(sku)
SELECT b->>'sku'
FROM   jsonb_array_elements(NEW.data->'bags') b

:

+3

More articles:


All Articles