在PostgreSQL中创建约束时,是否可以解决JSON数组的所有元素? [英] Is there a way to address all elements of JSON array when creating a constraint in PostgreSQL?
问题描述
PostgreSQL是否提供任何符号/方法对JSON数组的每个元素施加约束?
Does PostgreSQL provide any notation/method for putting a constraint on each element of a JSON array?
示例:
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
。我可以对单个数组项施加约束:
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.
很明显,我正在寻找某种通配符匹配任何JSON数组元素的运算符:
So obviously 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
我知道这可以通过将产品提取到单独的 products
表,其中带有指向 orders
的外键。但是我想知道是否可以在单个JSON列中实现,因此在设计数据库架构时可以牢记这一点。
I know that this can be done by extracting products to a separate products
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.
推荐答案
所以我问了关于PostgreSQL邮件列表的问题,如由Craig Ringer建议,我已经找到了答案。
So I asked this question on PostgreSQL mailing list, as suggested by Craig Ringer, and I've got the answer.
总之,解决方案是编写一个将JSON数组具体化为PostgreSQL的过程。数组:
In short the solution is to write a procedure which materializes JSON array to PostgreSQL array:
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
语句中使用该过程:
and use that procedure in CHECK
statment:
alter table orders add check (1 <= ALL(data_product_ids(data)));
有关其工作原理的更多详细信息,请关于PostgreSQL邮件列表的答案。归功于Joel Hoffman。
For more details on how this works se the answer on PostgreSQL mailing list. Credits to Joel Hoffman.
这篇关于在PostgreSQL中创建约束时,是否可以解决JSON数组的所有元素?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!