Уникальный индекс для встроенного объекта json

Question

Я сейчас тестирую Postgresql 9.4 beta2. Мне интересно, возможно ли создать уникальный индекс для встроенного объекта json?

Я создаю имя таблицы products:

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

Теперь я пытаюсь вставить объект JSON в столбец данных.

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

Тем не менее, я хочу sku сумок, чтобы быть уникальным. Это означает, что JSON не может быть вставлен в таблицы продуктов, потому что sku не уникален в этом случае.

Я пытался создать уникальный индекс, как показано ниже, но это не удалось.

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

Какие-либо предложения?

Answer 1

Ваша попытка создать UNIQUE INDEX на выражение должно было произойти сбой по нескольким причинам.

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

Первое и самое тривиальное, что...
data->'bags'->'sku'
ничего не ссылается. Вы можете ссылаться на первый элемент массива с помощью

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

или короче:

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

Но это выражение возвращает только первое значение массива.
Ваше определение: "Я хочу, чтобы 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:

• Может ли PostgreSQL иметь ограничение уникальности для элементов массива?

Адаптируйте только метод unnesting. Вместо:

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

Детали для этого:

• Соединение PostgreSQL с использованием JSONB