Thread: json and aggregate

Hi,

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;

Thanks!



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

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.

David Johnston wrote
> 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.

Ok, thanks! I'm pretty new to postgres "advanced" features an didn't even
know what a SRF was :)

Another question if you don't mind, I've written the following function to
convert many field to a json field and then group them based on another id.
I don't really it because I have to query each line...

---
CREATE OR REPLACE FUNCTION fn_tojson()    RETURNS TABLE(id integer, data json)
AS
$$
DECLARE    current_id integer;
BEGIN    FOR current_id IN(      SELECT nah.id FROM item_header nah    ) LOOP               id := current_id;
          data := (SELECT json_agg(x) FROM (SELECT message_id, dt_created,
 
lines, size, current_part, status, fk_item_cat_id FROM item na WHERE
na.fk_item_header_id = $1) x);                      RETURN NEXT;    END LOOP;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
---

I could use something like this (not tested!)
--
select fk_item_header_id as id, json_agg(row(message_id, dt_created, lines,
size, current_part, status, fk_item_cat_id)) as data from item group by
fk_item_header_id;
--
but my json object keys would be translated to f1, f2, ... and I really need
to keep the original ones.

Thanks again!



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

Diway wrote
> I could use something like this (not tested!)
> --
> select fk_item_header_id as id, json_agg(row(message_id, dt_created,
> lines, size, current_part, status, fk_item_cat_id)) as data from item
> group by fk_item_header_id;
> --

CREATE TYPE 

http://www.postgresql.org/docs/9.3/interactive/sql-createtype.html

Since you have a known set of columns you define a custom type that matches
the structure and then:

SELECT fk_item_header_id, json_agg(  ( message_id, dt_create, ...
)::custom_type  ) FROM ...;

Not tested as I do not have a JSON capable release available at the moment. 
But since the type information includes the column names the conversion to
JSON should keep those names intact.

David J.




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