PostgreSQL：按子查询生成的JSON数组属性排序报错求助

错误原因 #

你遇到的ERROR: column "images" does not exist，本质有两个核心问题：

1. json_array_elements是行集生成函数，会把单个JSON数组拆分成多行记录，无法直接用来排序原表的行；
2. PostgreSQL的解析顺序限制：虽然ORDER BY通常可以引用SELECT列表的别名，但对别名应用行集函数时，无法正确解析还未完全生成的别名结果。

可行解决方案 #

根据不同的排序逻辑，提供几种实用方案：

方案1：按JSON数组中特定位置的name排序 #

如果想以数组中第一个元素的name作为排序依据：

WITH my_data AS (
    SELECT
        id,
        (
            SELECT array_agg(json_build_object('id', id, 'name', name))
            FROM files
            WHERE id = ANY ("images")
            ORDER BY name
        ) AS "images"
    FROM my_table
)
SELECT id, "images"
FROM my_data
ORDER BY ("images" -> 0 ->> 'name') ASC;

这里"images" -> 0 ->> 'name'表示取JSON数组索引为0的元素的name属性值。

方案2：按JSON数组中name的聚合值排序（推荐） #

如果想按数组中name的最小值/最大值排序，用LATERAL子查询同时生成JSON数组和排序字段，性能更优：

SELECT
    t.id,
    img_arr AS "images"
FROM my_table t
LEFT JOIN LATERAL (
    SELECT 
        array_agg(json_build_object('id', id, 'name', name)) AS img_arr,
        MIN(name) AS sort_name -- 也可以替换为MAX(name)
    FROM files
    WHERE id = ANY (t."images")
    ORDER BY name
) f ON true
ORDER BY f.sort_name ASC;

方案3：按数组中name的排序拼接结果排序 #

如果想和子查询中ORDER BY name的顺序对应，按所有name的拼接字符串排序：

SELECT
    t.id,
    (
        SELECT array_agg(json_build_object('id', id, 'name', name))
        FROM files
        WHERE id = ANY (t."images")
        ORDER BY name
    ) AS "images"
FROM my_table t
ORDER BY (
    SELECT string_agg(name, ',')
    FROM files
    WHERE id = ANY (t."images")
    ORDER BY name
) ASC;

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