PostgreSQL中如何按JSONB列内email_subject排序特定用户数据？

问题原因 #

你当前的排序语句metadata->'emails'->>'email_subject'逻辑有误：metadata->'emails'返回的是JSON数组，直接提取email_subject只会取数组中第一个元素的对应值，而非匹配目标userId的那个email对象的email_subject，所以无法实现预期的排序效果。

解决方案 #

方法一：通过JOIN展开JSON数组并排序 #

将emails数组展开为单独的行，筛选出匹配目标userId的记录后，直接用对应email对象的email_subject排序：

SELECT d.*
FROM details d
JOIN jsonb_array_elements(d.metadata->'emails') AS x(email_obj)
  ON x.email_obj->>'recipients' LIKE '["%userId1@testsite.com%"]'
WHERE d.classification = 'EMAIL'
ORDER BY x.email_obj->>'email_subject' DESC;

注意：如果单条details记录包含多个匹配目标userId的email，该语句会返回多条重复的details数据。若需去重，可添加DISTINCT关键字：SELECT DISTINCT d.*

方法二：使用子查询获取排序字段 #

在ORDER BY子句中嵌入子查询，专门提取当前记录中匹配目标userId的email_subject：

SELECT d.*
FROM details d
WHERE d.classification = 'EMAIL'
  AND EXISTS (
    SELECT TRUE 
    FROM jsonb_array_elements(d.metadata->'emails') x 
    WHERE x->>'recipients' LIKE '["%userId1@testsite.com%"]'
  )
ORDER BY (
  SELECT x->>'email_subject'
  FROM jsonb_array_elements(d.metadata->'emails') x 
  WHERE x->>'recipients' LIKE '["%userId1@testsite.com%"]'
) DESC;

若单条记录存在多个匹配的email，该子查询会返回第一个匹配的email_subject；如需取最大/最小值，可改用MAX(x->>'email_subject')或MIN(...)。

优化建议：更严谨的数组匹配 #

用字符串LIKE匹配JSON数组容易出现误判（比如用户ID包含特殊字符时），建议使用PostgreSQL的jsonb_contains操作符@>来精准判断：
将条件x->>'recipients' LIKE '["%userId1@testsite.com%"]'替换为x->'recipients' @> '"userId1@testsite.com"'::jsonb。

优化后的方法一示例：

SELECT d.*
FROM details d
JOIN jsonb_array_elements(d.metadata->'emails') AS x(email_obj)
  ON x.email_obj->'recipients' @> '"userId1@testsite.com"'::jsonb
WHERE d.classification = 'EMAIL'
ORDER BY x.email_obj->>'email_subject' DESC;

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