Postgres: 构建用于 json 列的检查约束

Question

我希望将jsonb文档的根键作为约束进行检查，以确定是否包含任何文本元素，例如array ['likes'，'interests'，'activities'，'movies'，'music'，'television']。

create table foo(
    bar_json jsonb,
    constraint baz check(jsonb_object_keys(bar_json)::text[] @> array['likes','interests','activities','movies','music','television'])
);

上面那个不起作用，你能帮我吗？ 谢谢！

- Chris

Answer 1

使用Postgres中可用的JSON运算符可以更好地实现这一点。

特别针对您的用途：

?|：这些键/元素字符串中是否存在任何一个？

create table foo(
    bar_json jsonb,
    constraint baz check(bar_json ?| array['likes','interests','activities','movies','music','television'])
);

- cribwi

Answer 2

jsonb_object_keys(jsonb) 函数返回集合，而 check 中的表达式不能返回集合。 你试图将 jsonb_object_keys(jsonb)::text[] 转换是不正确的，你需要一个聚合函数。 使用聚合函数 bool_and() 创建一个布尔函数，并将其用作 check 表达式：

create or replace function check_bar_json(val jsonb)
returns boolean language sql as $$
    select 
        bool_and(elem = any(array['likes','interests','activities','movies','music','television']))
    from
        jsonb_object_keys(val) elem
$$;

create table foo(
    bar_json jsonb,
    constraint baz check(check_bar_json(bar_json))
);

- klin

---

能否不使用函数实现？ - Chris

实际上，函数是必要的。可以尝试将jsonb值解析为文本，但这太复杂且不值得。 - klin