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

[Bob Jones]

>>
>> 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


Awesome ! Thanks Charles for the answer, and Merlin for the
tweaking/additional insight.


-- 
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] Issue with json_agg() and ordering

[Merlin Moncure]

On Fri, Sep 1, 2017 at 6:22 AM, Charles Clavadetscher
 wrote:
> Hello
>
>> -Original Message-
>> From: pgsql-general-ow...@postgresql.org 
>> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bob Jones
>> Sent: Freitag, 1. September 2017 10:12
>> To: pgsql-general 
>> 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


-- 
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] Issue with json_agg() and ordering

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bob Jones
> Sent: Freitag, 1. September 2017 10:12
> To: pgsql-general 
> 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;

Regards
Charles

> 
> 
> I suspect It doesn't really matter what my table looks like for the purposes 
> of the above, but if you need something
> to go by:
> 
> create table my_table(
> last_name text,
> first name text,
> foo text,
> bar boolean
> );
> 
> Thanks !
> 
> Bob
> 
> 
> --
> 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