Is there a way to address all elements of JSON array when creating a constraint in PosgreSQL?
От | Maciej Szopinski |
---|---|
Тема | Is there a way to address all elements of JSON array when creating a constraint in PosgreSQL? |
Дата | |
Msg-id | CACYP-QPiPfgGz6U_Q87p4fwkiFP9JOx6h9T0e4zsXZi8QD2wTA@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Is there a way to address all elements of JSON array
when creating a constraint in PosgreSQL?
|
Список | pgsql-general |
Hello,
Does PostgreSQL provide any notation/method for putting a constraint on each element of a JSON array?
An example to illustrate:
create table orders(data json);
insert into orders values ('
{
"order_id": 45,
"products": [
{
"product_id": 1,
"name": "Book"
},
{
"product_id": 2,
"name": "Painting"
}
]
}
');
I can easily add a constraint on the order_id field:
alter table orders add check ((data->>'order_id')::integer >= 1);
Now I need to do the same with product_id. I can put constraint on idividual array items:
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.
So what I'm looking for is some kind of wildcard operator for matching any JSON array element:
alter table orders add check ((data->'products'->*->>'product_id')::integer >= 1);
-- ^ like this
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.
В списке pgsql-general по дате отправления: