From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Charles Clavadetscher <clavadetscher(at)swisspug(dot)org> |
Cc: | Bob Jones <r(dot)a(dot)n(dot)d(dot)o(dot)m(dot)d(dot)e(dot)v(dot)4+postgres(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Issue with json_agg() and ordering |
Date: | 2017-09-01 18:10:53 |
Message-ID: | CAHyXU0zcp4bmHxpfD+FfXwzqhDhKv0UEeht9q3kUGJ19-mdTjg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Sep 1, 2017 at 6:22 AM, Charles Clavadetscher
<clavadetscher(at)swisspug(dot)org> wrote:
> Hello
>
>> -----Original Message-----
>> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Bob Jones
>> Sent: Freitag, 1. September 2017 10:12
>> To: pgsql-general <pgsql-general(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter J. Holzer | 2017-09-01 18:17:34 | Re: pglogical bidirectional replication of sequences |
Previous Message | Peter J. Holzer | 2017-09-01 17:28:59 | Re: pglogical bidirectional replication of sequences |