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
-----------------------------------------------
Attachments:
Israel Brewster.vcftext/directory; name="Israel Brewster.vcf"; x-unix-mode=0666Download
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
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Import Notes
Reply to msg id not found: CAKFQuwZ8kNH=_bk7pddwWZ6QBCriFKUo0WYedM+kqxPHrTxX5A@mail.gmail.com
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 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