Thread: [GENERAL] sub-select with multiple records, columns
I have two tables, a ticket table and a notes table, set up where each ticket can have multiple notes. I'm trying to come up with a query that returns the ticket fields as well as a field that is an array type field with the values being json-encoded note records. I've come up with the following subselect query, which works:
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... :-)
-----------------------------------------------
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-----------------------------------------------
Attachment
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
On Mon, Jun 19, 2017 at 1:32 PM, David G. Johnston <david.g.johnston@gmail.com> wrote: > On Mon, Jun 19, 2017 at 1:29 PM, Thomas Kellerer <spam_eater@gmx.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.
On Jun 19, 2017, at 12:29 PM, Thomas Kellerer <spam_eater@gmx.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 ...) Thanks (and to David G. Johnston). Didn't realize I could do that, but it makes perfect sense. > > 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. You may be right. Actually, my first thought (and the ideal here) was to simply have an array of rows or the like. That is,wind up with a data structure where I could in my code do something like record['notes']['username'], or perhaps record['notes'][1].However, while I didn't get any errors when I tried that, the parsing of the results fell apart at somepoint in the chain - I wound up with strings containing a bunch of escaped and double-escaped quotes and the like. Addingthe to_json simply converted the rows to json strings, which I can work with easily enough. Since I do still have toparse the json anyway, perhaps making the entire array be a single json object that I could parse once would be a betterapproach. > > Thomas > > > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general