Ordering by JSON property from sub-select

Question

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)?

Could you please post result of select without ORDER BY, at least few rows? So that we have an idea what you get and what a the column names. — bracko, Dec 08 '22 at 17:56
Post your table description (ddl) and a few lines of sample data. Post the results of that data. All as text - **no images**. — Belayer, Dec 08 '22 at 18:19

score 1 · Accepted Answer · answered Dec 09 '22 at 03:27

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?

That's amazing - thank you so much for explaining the to me! Number 2 was particularly important - that was not a part of SQL that I was aware of. — Allan Jardine, Dec 09 '22 at 10:32

Ordering by JSON property from sub-select

1 Answers1