在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屋！