Re: [GENERAL] sub-select with multiple records, columns - Mailing list pgsql-general

Israel Brewster schrieb am 19.06.2017 um 22:17:
> SELECT
> ...
> (SELECT
> array_agg(to_json(row(notedate,username,note)))
> FROM sabrenotes
> INNER JOIN users ON author=users.id
> WHERE ticket=sabretickets.id ) notes
> FROM tickets
> WHERE ...
>
> The only problem with this query is that the notes aren't sorted. Of
> course, simply adding an ORDER BY clause to the sub-select doesn't
> work - it throws an error about needing to use notedate in a GROUP BY
> clause or aggregate function. Is there some way I can get sorting as
> well here? Of course, I could just run a second query to get the
> notes, and combine in code, but that's no fun... :-)

You can supply an ORDER BY to an aggregate function:

    array_agg(to_json(row(notedate,username,note)) order by ...)

I have to admit, that I fail to see the the advantage of an array of JSON objects, rather then having a single json
withthe elements inside. 

json_object_agg() or json_agg() might be better suited for this.

Thomas