一个例子：
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做同样的事情.我可以对个别数组项进行约束：
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
我知道这可以通过将产品提取到具有订单外键的单独产品表来完成.但我想知道在单个JSON列中是否可以实现这一点,因此在设计数据库模式时我可以牢记这一点.
简而言之,解决方案是编写一个将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)));
有关如何运作的详细信息,请参阅the answer on PostgreSQL mailing list. Joel Hoffman的致谢.
