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. Thomas
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
