Re: [GENERAL] sub-select with multiple records, columns
On Jun 19, 2017, at 12:29 PM, Thomas Kellererwrote: > > 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 with the 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 some point in the chain - I wound up with strings containing a bunch of escaped and double-escaped quotes and the like. Adding the to_json simply converted the rows to json strings, which I can work with easily enough. Since I do still have to parse the json anyway, perhaps making the entire array be a single json object that I could parse once would be a better approach. > > Thomas > > > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sub-select with multiple records, columns
On Mon, Jun 19, 2017 at 1:32 PM, David G. Johnstonwrote: > On Mon, Jun 19, 2017 at 1:29 PM, Thomas Kellerer 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sub-select with multiple records, columns
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 ) notesFROM ticketsWHERE ...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 BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD