PostgreSQL中如何通过SELECT语句从JSONB数组取值

你的查询返回空结果是因为使用的??|操作符不符合需求场景：

• ??|操作符的作用是检查JSONB对象的顶层键是否匹配数组中的任意元素，但你这里的data->'my_array'是一个JSONB数组，而非JSONB对象，所以这个操作符无法识别数组元素内部的my_array_id属性，导致条件判断错误。

下面是几种正确的写法，都能实现“找出my_array中不存在my_array_id为'12345678'的记录”的需求：

方法一：使用jsonb_path_exists（PostgreSQL 12+支持） #

通过JSON路径直接检查数组中是否存在匹配的元素，再取反：

SELECT *
FROM my_table
WHERE NOT jsonb_path_exists(data, '$.my_array[*] ? (@.my_array_id == "12345678")');

方法二：使用jsonb_array_elements展开数组+EXISTS子查询 #

将JSONB数组展开为行，再判断是否存在目标元素，最后取反：

SELECT t.*
FROM my_table t
WHERE NOT EXISTS (
    SELECT 1
    FROM jsonb_array_elements(t.data->'my_array') elem
    WHERE elem->>'my_array_id' = '12345678'
);

方法三：使用@>包含操作符 #

构造一个包含目标属性的JSONB数组，检查原数组是否包含该元素，再取反：

SELECT *
FROM my_table
WHERE NOT (data->'my_array' @> '[{"my_array_id": "12345678"}]'::jsonb);

验证说明 #

你的测试数据中存在my_array_id为'12345678'的元素，所以执行上述查询会返回空结果（符合预期，因为没有满足“不存在该id”的记录）；如果插入一条不包含该id的数据，就能正确查询出结果。

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