Search Postgresql Archives

Re: sub-select with multiple records, columns

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux