Re: sub-select with multiple records, columns

On Jun 19, 2017, at 12: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 ...)

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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general