如何在PostgreSQL查询中按产品created_at排序类别？

你的原查询存在两个关键问题，导致无法按预期排序：

1. CTE p 中的 order by p.created_at desc 不会影响后续的 jsonb_agg 结果顺序——PostgreSQL 中，不带 limit 的 CTE 排序会被优化器忽略，聚合时不会保留这个顺序。
2. 排序子句 p.products->>'created_at' 是直接取 JSON 数组的第一个元素的创建时间，但数组元素顺序无保证，且空数组会返回 null，排序逻辑不可靠。

要实现“包含最新创建产品的类别排在最前面”，可以直接在关联子查询中计算每个类别的最新产品创建时间，用这个值作为排序依据，同时确保产品数组内部按创建时间降序排列：

select
  c.*,
  coalesce(p.products, '[]'::jsonb) as products
from categories as c
left join lateral (
  select 
    jsonb_agg(p.* order by p.created_at desc) as products,
    max(p.created_at) as latest_product_created_at
  from products as p
  where p.category_id = c.id
) as p on true
order by p.latest_product_created_at desc nulls last;

关键说明： #

• jsonb_agg(p.* order by p.created_at desc)：在聚合时直接指定排序，确保返回的产品数组是按创建时间从新到旧排列的。
• max(p.created_at)：获取当前类别下最新产品的创建时间，用这个值来排序类别，保证最新产品的类别排在最前。
• nulls last：将没有产品的类别排在所有有产品的类别之后（如果需要把无产品的类别放前面，可以改成 nulls first）。

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