时间戳

首页 > 解决方案 > 是否可以添加约束以使用 Postgres 将每个插入的对象检查到 JSON 数组中？

arrays - 是否可以添加约束以使用 Postgres 将每个插入的对象检查到 JSON 数组中？

问题描述

我试图弄清楚如何在我的表中添加一个约束，该表在 Postgres 中包含一个 JSON 对象。我希望该约束确保 jpg 将具有非 null md5 属性。

我试图检查子数组，但我无法让它在数组上工作。如果使用取消引用箭头运算符（table->'jpg'->0 ?'md5'），我可以测试该属性。我也尝试使用 jsonb_array_elements() 函数，但在约束语句中不允许使用它。

例如：

{
    jpg: [
        {
            md5: "some md5",
            ...
        }
    ]
}

我会用这个：

ALTER TABLE table ADD CONSTRAINT md5_is_defined CHECK(table->'jpg'->0 ? 'md5')

但我不想只检查第一个插入的元素

我想知道在这个检查语句中使用 jsonb_array_elements 是否有一些技巧。是的，我意识到我应该标准化我的数据。我试图这样做，但为其编写语句变成了一个怪物。

标签： arrayspostgresqlconstraintsjsonb

解决方案

数组在 SQL 中处理起来非常麻烦，因为它们基本上违反了 SQL 所做的任何事情。检查数组中的每个元素是否存在某些东西通常强烈表明数组是错误的选择。

使用 Postgres 12，这非常容易做到：

alter table the_table 
    add constraint md5_is_defined 
    check (jsonb_path_exists(the_column, '$.jpg[*].md5'));

对于旧版本，我唯一能想到的就是创建一个检查存在的函数，然后在检查约束中使用该函数：

create or replace function check_md5(p_input jsonb)
  returns boolean
as
$$
select exists (select *
               from jsonb_array_elements(p_input -> 'jpg') as t(e) 
               where e ? 'md5');
$$
language sql
immutable;

然后你可以像这样使用它：

alter table the_table 
    add constraint md5_is_defined 
    check (check_md5(the_column));

编辑

要检查特定键是否包含非空数组，您可以使用以下内容：

alter table the_table
    add constraint non_empty_array
    check (jsonb_path_exists(the_column, '$.event ? (@.teams.type() == "array" && @.teams.size() > 0)'));

检查@.teams.type() == "array"是必要的，因为一个简单的{"teams": "yes"}也返回一个非零值size()

推荐阅读