Обсуждение: [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 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
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
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
>> >> 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.