Ordering by JSON property from sub-select-CodePudding

I'm having difficulty understanding how I can order a postgres result by the properties of an array of JSON objects that I've built in a sub-select. This is a simplification of what I have:

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
ORDER BY json_array_elements("images") ->> 'name' ASC;

But that results in the error:

ERROR: column "images" does not exist

json_array_elements presumably can only operate on actual columns in a table, not a result?

So is there anyway order by the name property at the top level (I'm not bothered about the order of the sub-select - that's actually selecting from a CTE called files which has an ORDER BY)?

CodePudding user response：

Three problems:

1.) After aggregating multiple names in your JSON array images, it's undefined which name you would want to use for sorting. (I chose the minimum per group.)

2.) SQL allows to use input columns, expressions based on input columns, or output columns in the ORDER BY clause, but not expressions based on output columns. (I computed the needed value separately in the LATERAL subquery.) See:

ORDER BY lower case of output column

3.) Your columns names are not table-qualified. So while we can't see table definitions, it's not entirely clear they resolve to the columns they are supposed to. (I table-qualified all columns to eliminate possible ambiguity.)

This would work:

SELECT t.id
     , f.images
FROM   my_table t
CROSS  JOIN LATERAL (
   SELECT array_agg(json_build_object('id', f.id, 'name', f.name)) AS images
        , min(f.name) AS min_name
   FROM   files f
   WHERE  f.id = ANY (t.images)
   ORDER  BY f.name
   ) f
ORDER  BY f.min_name;

See:

What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?