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

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;

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