如何在PostgreSQL的jsonb_agg中对聚合评论按date_updated升序排序？

问题 #

我已经扩展了SQL查询，用来返回应用中每个媒体文件的关联评论，原查询如下：

SELECT m.media_id, m.name, m.path, m.original_name, m.description, m.privacy, m.size, m.type, m.file_type, m.file_size, 
       m.width, m.height, m.date_created, m.date_uploaded, m.media_set_id, r.rating,
       jsonb_agg(distinct jsonb_build_object('comment', c.comment, 'parent_id', c.parent_id, 'user_id', c.user_id, 'date_updated', c.date_updated)) filter (where c.comment is not null and c.date_deleted is null) as comments,
       array_agg(t.tag) filter (where t.tag is not null) as tags 
FROM media m JOIN users u USING (user_id) 
LEFT JOIN media_tags mt USING (media_id) 
LEFT JOIN tags t USING (tag_id) 
LEFT JOIN ratings r using (media_id, user_id) 
LEFT JOIN comments c using (media_id)
WHERE u.user_id = 'b14c1548-d433-4bc2-9059-9091262e7305' AND m.date_deleted IS NULL AND m.privacy != 'support' 
GROUP BY m.media_id, m.name, m.path, m.original_name, m.description, m.privacy, m.size, m.type, m.file_type, m.file_size, 
         m.width, m.height, m.date_created, m.date_uploaded, m.media_set_id, r.rating 
ORDER BY date(m.date_created) DESC, m.date_created ASC, m.date_uploaded ASC

其中生成评论数组的核心代码是：

jsonb_agg(distinct jsonb_build_object('comment', c.comment, 'parent_id', c.parent_id, 'user_id', c.user_id, 'date_updated', c.date_updated)) filter (where c.comment is not null and c.date_deleted is null) as comments,

当前返回的评论数组示例（包含3条评论）：

[{"comment": "Comment 1 (Jay)", "user_id": "e8e4144d-f610-4c7f-a6a6-77355e16c297", "parent_id": null, "date_updated": "2024-02-16T11:30:09.406089-06:00"}, {"comment": "Comment 1 (Rob)", "user_id": "b14c1548-d433-4bc2-9059-9091262e7305", "parent_id": null, "date_updated": "2024-02-16T11:27:33.465173-06:00"}, {"comment": "Comment 2 (Rob)", "user_id": "b14c1548-d433-4bc2-9059-9091262e7305", "parent_id": null, "date_updated": "2024-02-16T11:31:25.197722-06:00"}]

现在需要实现的是：将每组评论按date_updated字段升序排序，请问该如何操作？

解决方案 #

可以通过在jsonb_agg中添加ORDER BY子句来实现评论数组的排序，同时保留原有的DISTINCT和过滤逻辑。

修改后的核心代码： #

jsonb_agg(distinct jsonb_build_object('comment', c.comment, 'parent_id', c.parent_id, 'user_id', c.user_id, 'date_updated', c.date_updated) ORDER BY c.date_updated ASC) filter (where c.comment is not null and c.date_deleted is null) as comments,

完整修改后的SQL： #

SELECT m.media_id, m.name, m.path, m.original_name, m.description, m.privacy, m.size, m.type, m.file_type, m.file_size, 
       m.width, m.height, m.date_created, m.date_uploaded, m.media_set_id, r.rating,
       jsonb_agg(distinct jsonb_build_object('comment', c.comment, 'parent_id', c.parent_id, 'user_id', c.user_id, 'date_updated', c.date_updated) ORDER BY c.date_updated ASC) filter (where c.comment is not null and c.date_deleted is null) as comments,
       array_agg(t.tag) filter (where t.tag is not null) as tags 
FROM media m JOIN users u USING (user_id) 
LEFT JOIN media_tags mt USING (media_id) 
LEFT JOIN tags t USING (tag_id) 
LEFT JOIN ratings r using (media_id, user_id) 
LEFT JOIN comments c using (media_id)
WHERE u.user_id = 'b14c1548-d433-4bc2-9059-9091262e7305' AND m.date_deleted IS NULL AND m.privacy != 'support' 
GROUP BY m.media_id, m.name, m.path, m.original_name, m.description, m.privacy, m.size, m.type, m.file_type, m.file_size, 
         m.width, m.height, m.date_created, m.date_uploaded, m.media_set_id, r.rating 
ORDER BY date(m.date_created) DESC, m.date_created ASC, m.date_uploaded ASC

说明： #

• 在jsonb_agg函数内部，紧跟要聚合的JSON对象之后添加ORDER BY c.date_updated ASC，即可让聚合后的JSON数组按照评论的date_updated字段升序排列。
• 原有的DISTINCT和FILTER条件保持不变，确保只保留有效且不重复的评论。

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