Есть ли способ адресовать все элементы массива JSON при создании ограничения в PostgreSQL?

Предоставляет ли PostgreSQL какую-либо нотацию/метод для наложения ограничения на каждый элемент массива JSON?

Пример:

create table orders(data json);

insert into orders values ('
{
    "order_id": 45,
    "products": [
        {
            "product_id": 1,
            "name": "Book"
        },
        {
            "product_id": 2,
            "name": "Painting"
        }
    ]
}
');

Я могу легко добавить ограничение на поле order_id:

alter table orders add check ((data->>'order_id')::integer >= 1);

Теперь мне нужно сделать то же самое с product_id. Я могу установить ограничение на отдельные элементы массива:

alter table orders add check ((data->'products'->0->>'product_id')::integer >= 1);
alter table orders add check ((data->'products'->1->>'product_id')::integer >= 1);
-- etc.

Итак, очевидно, что я ищу какой-то оператор подстановочных знаков для соответствия любому элементу массива JSON:

alter table orders add check ((data->'products'->*->>'product_id')::integer >= 1);
--                                               ^ like this

Я знаю, что это можно сделать, извлекая продукты в отдельную таблицу products с внешним ключом на orders. Но я хочу знать, возможно ли это в пределах одного столбца JSON, поэтому я могу помнить об этом при разработке схемы базы данных.


postgresql json postgresql-9.3
person Maciej Sz    schedule 20.01.2014    source источник
comment
Я думаю, вам понадобится подзапрос, чтобы сделать это в данный момент. Я бы поместил свою проверку в процедуру PL/PgSQL, которая зацикливается на каждом элементе массива, и использовал бы это в своем ограничении CHECK. Рассмотрите возможность поднятия этого варианта использования в списке рассылки.   -  person Craig Ringer    schedule 21.01.2014

Ответы (2)


arrow_upward
3
arrow_downward

Поэтому я задал этот вопрос в списке рассылки PostgreSQL, как предложено Крейгом Рингером, и у меня есть ответ.

Короче говоря, решение состоит в том, чтобы написать процедуру, которая материализует массив JSON в массив PostgreSQL:

create function data_product_ids(JSON) returns integer[] immutable  as $$
select array_agg((a->>'product_id')::integer) from
json_array_elements($1->'products') as a $$ language sql ;

и используйте эту процедуру в статусе CHECK:

alter table orders add check (1 <= ALL(data_product_ids(data)));

Подробнее о том, как это работает, см. ответ на PostgreSQL список рассылки. Кредиты Джоэл Хоффман.

person Maciej Sz    schedule 16.02.2014

arrow_upward
1
arrow_downward

От одного из разработчиков JSON для Postgres

Материал пути не поддерживает подстановочные знаки.

person waTeim    schedule 21.01.2014