如何对嵌套JSON列表中的created_at字段执行ORDER BY排序

针对你的PostgreSQL表结构（value字段为jsonb类型，包含approvals数组），分两种常见场景给出解决方案：

场景1：将每行的approvals数组按created_at排序 #

如果你需要把每个记录里的approvals数组元素按created_at字段重新排序，可以通过展开数组-排序-重新聚合的方式实现：

SELECT 
  id,
  jsonb_set(
    value,
    '{approvals}',
    (SELECT jsonb_agg(approval ORDER BY (approval->>'created_at')::integer)
     FROM jsonb_array_elements(value->'approvals') approval)
  ) AS value_with_sorted_approvals
FROM the_table;

代码说明： #

• jsonb_array_elements(value->'approvals')：将approvals数组拆分成独立的jsonb对象行
• ORDER BY (approval->>'created_at')::integer：将created_at字段转为整数后排序
• jsonb_agg(...)：把排序后的对象重新聚合成jsonb数组
• jsonb_set(...)：将原value字段中的approvals替换为排序后的数组

场景2：按approvals数组内的created_at值排序整个表的行 #

如果你需要基于approvals数组里的created_at值对表的记录进行排序（比如按数组中最大/最小的created_at排序），可以使用子查询提取对应值后排序：

示例：按数组中最大的created_at降序排序 #

SELECT *
FROM the_table
ORDER BY (
  SELECT MAX((approval->>'created_at')::integer)
  FROM jsonb_array_elements(value->'approvals') approval
) DESC;

示例：按数组中最小的created_at升序排序 #

SELECT *
FROM the_table
ORDER BY (
  SELECT MIN((approval->>'created_at')::integer)
  FROM jsonb_array_elements(value->'approvals') approval
) ASC;

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