在PostgreSQL中创建约束时，是否可以解决JSON数组的所有元素？

本文介绍了在PostgreSQL中创建约束时，是否可以解决JSON数组的所有元素？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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.