Re: sub-select with multiple records, columns

On Mon, Jun 19, 2017 at 1:32 PM, David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Mon, Jun 19, 2017 at 1:29 PM, Thomas Kellerer <spam_eater(at)gmx(dot)net> 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.