agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedjson and aggregate
4+ messages / 2 participants
[nested] [flat]
* json and aggregate
@ 2013-11-04 20:22 Diway <diway@diway.net>
0 siblings, 1 reply; 4+ messages in thread
From: Diway @ 2013-11-04 20:22 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: json and aggregate
@ 2013-11-04 20:38 David Johnston <polobo@yahoo.com>
parent: Diway <diway@diway.net>
0 siblings, 1 reply; 4+ messages in thread
From: David Johnston @ 2013-11-04 20:38 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: json and aggregate
@ 2013-11-04 21:05 Diway <diway@diway.net>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Diway @ 2013-11-04 21:05 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: json and aggregate
@ 2013-11-04 21:15 David Johnston <polobo@yahoo.com>
parent: Diway <diway@diway.net>
0 siblings, 0 replies; 4+ messages in thread
From: David Johnston @ 2013-11-04 21:15 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2013-11-04 21:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-11-04 20:22 json and aggregate Diway <diway@diway.net>
2013-11-04 20:38 ` David Johnston <polobo@yahoo.com>
2013-11-04 21:05 ` Diway <diway@diway.net>
2013-11-04 21:15 ` David Johnston <polobo@yahoo.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox