Date: Mon, 4 Nov 2013 12:38:16 -0800 (PST)
From: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Message-ID: <1383597496358-5776905.post@n5.nabble.com>
In-Reply-To: <1383596556949-5776903.post@n5.nabble.com>
References: <1383596556949-5776903.post@n5.nabble.com>
Subject: Re: json and aggregate
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

Diway wrote
> Why is the following query not working ?
> select sum((json_array_elements(data)->>'lines')::integer) as test from
> test2 where id = 2;
> ERROR:  set-valued function called in context that cannot accept a set
> 
> ('data' is obviously a json datatype)
> 
> On the other side, this one is OK but I don't like it ;-)
> select sum(value) from (select
> (json_array_elements(data)->>'lines')::integer as value from test2 where
> id = 2) x;

How technical an answer do you want?

Short answer is that GROUP BY/aggregates cannot process a
set-returning-function (SRF) in the select-list.  You have move the SRF into
the associated FROM clause and let the individual rows feed from there into
the GROUP BY/aggregates.