Re: json and aggregate

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.

From the documentation:

json_array_elements(json) [returns] SETOF json

the "SETOF" is the indicator of a SRF.  The reference to "set-valued
function" is another term for this concept.

So, yes, the version you do not like as well is required.  First you break
apart the json THEN you can aggregate.

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/json-and-aggregate-tp5776903p5776905.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.