unique index on embedded json object

Question

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?

A unique index won't work here, because there's *only one value*. You can't add multiple entries to an index for a single row. For this you would need a `check` constraint - but that can only operate on a single row. There's no easy way to say "I want this subfield to be unique across all json objects in all rows". — Craig Ringer, Sep 21 '14 at 12:35
Please don't use `oid` for your own columns. That is an internal name used by Postgres and will cause lots of confusion (if not problems) — , Sep 21 '14 at 12:36
I think I have to change my design. Thank you for helping me. — JasonLee, Sep 21 '14 at 14:59

score 5 · Accepted Answer · edited Jun 20 '20 at 09:12

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:

Can PostgreSQL have a uniqueness constraint on array elements?

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:

PostgreSQL joining using JSONB

unique index on embedded json object

1 Answers1

Possible solution