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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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