Re: Is there a way to address all elements of JSON array when creating a constraint in PosgreSQL?

Поиск
Список
Период
Сортировка
От Joel Hoffman
Тема Re: Is there a way to address all elements of JSON array when creating a constraint in PosgreSQL?
Дата
Msg-id CAEF8rJtwAC7om=MLt2a03XQTgVL1cYhLypBHv69-ZWrixztG6g@mail.gmail.com
обсуждение исходный текст
Ответ на Is there a way to address all elements of JSON array when creating a constraint in PosgreSQL?  (Maciej Szopinski <maciej.sz@gmail.com>)
Список pgsql-general
Дерево обсуждения
On Tue, Jan 21, 2014 at 1:34 AM, Maciej Szopinski <maciej.sz@gmail.com> wrote:
Hello,

Does PostgreSQL provide any notation/method for putting a constraint on each element of a JSON array?

An example to illustrate:


[...] 
I know that this can be done by extracting products to a separate table with a foreign key to orders. But I want to know if this is possible within single JSON column, so I can keep that in mind when designing a database schema.


If you write a short function to help, it's possible.  You would need a subquery to make this assertion, but you can't add one directly as a check constraint:

create table orders (data JSON);

alter table orders add check (1 <= ALL((select array_agg((a->>'product_id')::integer) from json_array_elements(data->'products') as a)));
ERROR:  cannot use subquery in check constraint

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 ;
CREATE FUNCTION

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

insert into orders (data) values ('{"products": [{ "product_id":1 }, { "product_id":2 }]}');
INSERT 0 1

insert into orders (data) values ('{"products": [{ "product_id":0 }, { "product_id":2 }]}');
ERROR:  new row for relation "orders" violates check constraint "orders_data_check"
DETAIL:  Failing row contains ({"products": [{ "product_id":0 }, { "product_id":2 }]}).

 

В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: pg_upgrade fails: "Mismatch of relation OID in database" - 9.2.4 to 9.3.2
Следующее
От: Kevin Field
Дата:
Сообщение: Re: client_min_messages not suppressing messages in psql nor pgAdminIII