CODEWITHSUNDEEP
jsonpostgresqlindexingunique-indexpostgresql-9.4
JasonLee
JasonLee

Reputation: 43

unique index on embedded json object

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?

I create a table name 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?

Upvotes: 4

Views: 3204

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

Your attempt to create a UNIQUE INDEX on the expression was bound to fail for multiple reasons.

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

The first and most trivial being that ...
data->'bags'->'sku'
does not reference anything. You could reference the first element of the array with

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

or shorter:

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

But that expression only returns the first value of the array.
Your definition: "I want sku of bags to be unique" .. is unclear. Do you want the value of sku to be unique? Within one JSON object or among all json objects in the column data? Or do you want to restrict the array to a single element with an sku?

Either way, neither of these goals can be implemented with a simple UNIQUE index.

Possible solution

If you want sku values to be unique across all json arrays in data->'bags', there is a way. Unnest the array and write all individual sku values to separate rows in a simple auxiliary table with a unique (or PK) constraint:

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

This table may be useful for additional purposes.
Here is a complete code example for a very similar problem with plain Postgres arrays:

Only adapt the unnesting technique. Instead of:

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;

Use:

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

Details for that:

Upvotes: 5

Related Questions