Order specific columns in json_agg

I want to sort the rows inside the json_agg function.

I found this:
PostgreSQL : select columns inside json_agg

Which gave me a solution to selecting specific columns for json_agg, but now I want to give the aggregated data a specific order. The "distinct" does work the "order by" does not.

json_agg(DISTINCT (colA, colB))

This does not:

json_agg(DISTINCT (colA, colB) ORDER BY colA ASC, colB ASC)

Is there a way to sort the aggregated data?

Without knowing exactly what you meant by 'The "distinct" does work the "order by" does not.', I guess you get an error:


ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list

It is more or less self-explanatory. Try

json_agg(DISTINCT (colA, colB) ORDER BY (colA, colB))

instead - you don't aggregate individual columns in json_agg() but a record, and should order by that one.

StackExchange Database Administrators Q#81732, answer score: 10