在 PostgreSQL 中创建约束时，有没有办法处理 JSON 数组的所有元素？

PostgreSQL 是否提供任何符号/方法来施加约束eachJSON 数组的元素？

一个例子：

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 field:

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 邮件列表上的这个问题 http://www.postgresql.org/message-id/CACYP-QPiPfgGz6U_Q87p4fwkiFP9JOx6h9T0e4zsXZi8QD2wTA@mail.gmail.com, as 克雷格·林格建议 https://stackoverflow.com/questions/21245997/is-there-a-way-to-address-all-elements-of-json-array-when-creating-a-constraint#comment32009083_21245997，我已经得到答案了。

简而言之，解决方案是编写一个将 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 邮件列表上的答案 http://www.postgresql.org/message-id/CAEF8rJtwAC7om=MLt2a03XQTgVL1cYhLypBHv69-ZWrixztG6g@mail.gmail.com。感谢乔尔·霍夫曼。