PostgreSQL jsonb数组非包含查询返回空结果集问题

首先咱们先理清问题的核心：你当前用的?操作符其实并不适合用来检查jsonb数组是否包含指定元素——这可能是导致NOT查询异常的根源之一，另外还要注意字段值为NULL的情况。

1. 先纠正数组包含检查的正确操作符 #

PostgreSQL的?操作符本质是检查字符串是否是jsonb对象的顶层键，而非用来判断数组元素是否存在。虽然你当前用worker_ids ? '1'能返回结果，这可能是某些版本的隐式行为（或者你的字段值实际并非纯数组），但正确检查jsonb数组是否包含指定元素应该用以下两种方式：

方式一：使用@>操作符（包含操作符） #

@>用于判断左侧的jsonb值是否包含右侧的jsonb值，对于数组来说，就是检查左侧数组是否包含右侧指定的元素：

-- 查询包含'1'的行
SELECT * FROM task WHERE worker_ids @> '["1"]'::jsonb;

-- 查询不包含'1'的行
SELECT * FROM task WHERE NOT (worker_ids @> '["1"]'::jsonb);

方式二：使用?|操作符（任一匹配） #

?|接受一个text数组，检查jsonb数组是否包含其中任一元素：

-- 查询包含'1'的行
SELECT * FROM task WHERE worker_ids ?| array['1'];

-- 查询不包含'1'的行
SELECT * FROM task WHERE NOT (worker_ids ?| array['1']);

2. 为什么你的NOT查询返回空？ #

最可能的两个原因：

• 操作符使用错误：?操作符不适合数组检查，当你用NOT (worker_ids ? '1')时，实际是在筛选"不存在键'1'的jsonb值"，但数组本身没有键，逻辑上会匹配所有数组行，但这和你说的返回空矛盾，那更可能是第二个原因：
• 字段存在NULL值：如果你的task表中，有些行的worker_ids字段是NULL，那么worker_ids ? '1'会返回NULL，而NOT NULL仍然是NULL——PostgreSQL的WHERE子句只会保留结果为true的行，NULL的行不会被包含。如果你的表中所有行要么是包含'1'的数组，要么是NULL，那么NOT (worker_ids ? '1')就会返回空。

3. 完整的解决方案 #

如果你想要筛选不包含'1'的行，同时包含worker_ids为NULL的行，需要显式处理NULL的情况：

-- 方案1：使用@>操作符，包含NULL行
SELECT * FROM task 
WHERE worker_ids IS NULL OR NOT (worker_ids @> '["1"]'::jsonb);

-- 方案2：使用?|操作符，包含NULL行
SELECT * FROM task 
WHERE worker_ids IS NULL OR NOT (worker_ids ?| array['1']);

4. 验证你的数据 #

可以先执行以下查询确认你的字段值类型和NULL情况，方便排查：

-- 查看worker_ids的类型分布和NULL数量
SELECT 
  jsonb_typeof(worker_ids) AS type,
  COUNT(*) AS count,
  SUM(CASE WHEN worker_ids IS NULL THEN 1 ELSE 0 END) AS null_count
FROM task
GROUP BY jsonb_typeof(worker_ids);

内容的提问来源于stack exchange，提问作者Aliaksei Stadnik