在PostgreSQL中创建约束时,有没有办法解决JSON数组的所有元素?
发布时间:2020-12-13 15:54:39 所属栏目:百科 来源:网络整理
导读:PostgreSQL是否提供了对 JSON数组的每个元素设置约束的任何符号/方法? 一个例子: create table orders(data json);insert into orders values ('{ "order_id": 45,"products": [ { "product_id": 1,"name": "Book" },{ "product_id": 2,"name": "Painting"
PostgreSQL是否提供了对
JSON数组的每个元素设置约束的任何符号/方法?
一个例子: 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列中是否可以实现这一点,因此在设计数据库模式时我可以牢记这一点. 解决方法
所以我问
this question on PostgreSQL mailing list,作为
suggested by Craig Ringer,我得到了答案.
简而言之,解决方案是编写一个将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的致谢. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |