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

Я сейчас тестирую 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') )

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


postgresql json indexing postgresql-9.4 unique-index
person JasonLee    schedule 21.09.2014    source источник
comment
Уникальный индекс здесь не подойдет, потому что здесь только одно значение. Вы не можете добавить несколько записей в индекс для одной строки. Для этого вам понадобится ограничение check, но оно может работать только с одной строкой. Нет простого способа сказать, что я хочу, чтобы это подполе было уникальным для всех объектов json во всех строках.   -  person Craig Ringer    schedule 21.09.2014
comment
Пожалуйста, не используйте oid для своих собственных столбцов. Это внутреннее имя, используемое Postgres, и оно вызовет много путаницы (если не проблем).   -  person a_horse_with_no_name    schedule 21.09.2014
comment
Я думаю, что я должен изменить свой дизайн. Спасибо за помощь мне.   -  person JasonLee    schedule 21.09.2014

Ответы (1)


arrow_upward
5
arrow_downward

Ваша попытка создать 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 было уникальным? В пределах одного объекта 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

Подробная информация о этом:

person Erwin Brandstetter    schedule 27.12.2014