如何查询PostgreSQL中JSON列多维数组含指定字符串的行？

你之前的SQL语句无效的原因是：?操作符用于检查JSON对象是否包含指定键，或者JSON数组是否包含指定字符串元素，但你的lastMessages是对象数组，不是字符串数组，所以无法匹配到对象里的replyPreview值。

下面是三种可行的实现方式：

方法一：使用@>包含操作符（推荐，支持索引） #

如果你的session_data列创建了GIN索引，这种方式性能最优：

SELECT * 
FROM dialog_sessions 
WHERE session_data @> '{"lastMessages": [{"replyPreview": "response-text-z"}]}'::jsonb 
LIMIT 100;

@>操作符会检查session_data是否包含指定的JSON结构，只要lastMessages数组里存在任意一个对象的replyPreview等于目标值，就会匹配该行。

方法二：展开数组后过滤（适合复杂条件） #

通过jsonb_array_elements将数组展开为行，再过滤属性值，最后用DISTINCT去重（避免数组多匹配导致重复行）：

SELECT DISTINCT ds.* 
FROM dialog_sessions ds,
     jsonb_array_elements(ds.session_data->'lastMessages') AS msg
WHERE msg->>'replyPreview' = 'response-text-z'
LIMIT 100;

方法三：使用EXISTS子查询（无重复行） #

用子查询检查数组中是否存在符合条件的对象，不需要去重：

SELECT * 
FROM dialog_sessions ds
WHERE EXISTS (
    SELECT 1 
    FROM jsonb_array_elements(ds.session_data->'lastMessages') AS msg
    WHERE msg->>'replyPreview' = 'response-text-z'
)
LIMIT 100;

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