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?

  • Your question is unclear. There is not ORDER BY construct attached to the DISTINCT clause directly. There is only the ORDER BY attached to the query. Please provide your full query, some sample data the desired result. – Erwin Brandstetter Nov 16 '14 at 13:51

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.