Re: [GENERAL] sub-select with multiple records, columns

[Israel Brewster]

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

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

[David G. Johnston]

On Mon, Jun 19, 2017 at 1:32 PM, David G. Johnston
 wrote:
> 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

[Thomas Kellerer]

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