PostgreSQL：如何查询不含指定键值对的jsonb数组列数据

针对你提出的需求——筛选出attributes列（jsonb数组类型）中所有数组对象都不包含"foo": "foo"键值对的行，这里提供两种高效的解决方案，避免用文本匹配的低效方式：

方法一：使用NOT EXISTS子查询 #

通过展开jsonb数组，检查是否存在匹配的对象，若不存在则保留该行：

SELECT *
FROM your_table t
WHERE NOT EXISTS (
    SELECT 1
    FROM jsonb_array_elements(t.attributes) elem
    WHERE elem @> '{"foo": "foo"}'::jsonb
);

说明： #

• jsonb_array_elements(t.attributes)：将数组中的每个JSON对象拆分为单独的行
• elem @> '{"foo": "foo"}'::jsonb：判断单个JSON对象是否包含指定的键值对（@>是jsonb的包含操作符）
• NOT EXISTS：确保数组中没有任何一个对象满足匹配条件，也就是所有对象都不包含"foo": "foo"

方法二：使用JSON路径函数 #

利用PostgreSQL的jsonb_path_exists函数，通过JSON路径表达式直接检查数组是否存在匹配元素，取反即可：

SELECT *
FROM your_table
WHERE NOT jsonb_path_exists(attributes, '$[*] ? (@.foo == "foo")');

说明： #

• $[*]：JSON路径语法，遍历数组中的所有元素
• ? (@.foo == "foo")：筛选出foo属性值等于"foo"的元素
• jsonb_path_exists返回true表示存在匹配元素，NOT取反后就得到所有元素都不匹配的行

性能优化建议 #

如果这类查询频率较高，可以给attributes列创建GIN索引，提升查询效率：

CREATE INDEX idx_attributes_gin ON your_table USING GIN (attributes);

以上两种方法都能精准筛选出你示例中的最后一行数据，且效率远高于文本匹配方式。

内容的提问来源于stack exchange，提问作者Mr.