Re: [GENERAL] Issue with json_agg() and ordering

On Fri, Sep 1, 2017 at 6:22 AM, Charles Clavadetscher
<clavadetscher@swisspug.org> wrote:
> Hello
>
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bob Jones
>> Sent: Freitag, 1. September 2017 10:12
>> To: pgsql-general <pgsql-general@postgresql.org>
>> Subject: [GENERAL] Issue with json_agg() and ordering
>>
>> Hi,
>>
>>
>> Could anyone give me a few pointers as to how I might resolve the following :
>>
>> select json_agg(my_table) from (my_table) where foo='test' and bar='f'
>> order by last_name asc, first_name asc;
>>
>> ERROR:  column "my_table.last_name" must appear in the GROUP BY clause or be used in an aggregate function LINE 1:
>> ...foo='f' order by last_name ...
>
> I guess that the order by should be in the aggregation.
>
> SELECT json_agg(a.* ORDER BY a.last_name, a.last_year DESC)
> FROM my_table a;

yes.  however, you would say, json_agg(a... not 'a.*').  The .*
notation only works in certain contexts, and is transformed at parse
time to, a.col1, a.col2, a.col3...  which would not work inside an
aggregation function which can only handle a single column or record.

merlin