On Mon, Jun 19, 2017 at 1:32 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote: > On Mon, Jun 19, 2017 at 1:29 PM, Thomas Kellerer <spam_eater@xxxxxxx> wrote: >> >> 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 with the elements inside. >> >> json_object_agg() or json_agg() might be better suited for this. >> You could also write: SELECT ..., ARRAY(SELECT to_json(...) [...] ORDER BY) AS notes FROM tickets David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
