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

Question

我正在尝试弄清楚如何向包含 Postgres 中的 JSON 对象的表添加约束。我希望该约束可确保 jpg 具有非空 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 是否有一些技巧。是的，我意识到我应该规范化我的数据。我尝试这样做，但为之编写语句变得很麻烦。

Answer 1

数组在 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()

的值为零