PostgreSQL ERROR: set-returning functions must appear at top level of FROM

Question

So I've got a postgres database in which I've got a jsonb field called details:

sensor | details
------------------
A      | [{"direction":"up", "result": 1.0}, {"direction":"up", "result": 2.0}]
B      | [{"direction":"up", "result": 3.0}, {"direction":"down", "result": 4.0}]
B      | [{"direction":"up", "result": 5.0}, {"direction":"up", "result": 6.0}, {"direction":"down", "result": 7.0}]
A      | [{"direction":"down", "result": 8.0}, {"direction":"left", "result": 9.0}]

I now need a sum of the result of all the up records and down records grouped by sensor.

So my query should result in this:

sensor | up_sum | down_sum
---------------------------
A      | 3.0      | 8.0
B      | 14.0     | 11.0

I need to somehow:

loop over the details array objects
filter by up and down
and then sum the results for those records per sensor

I guess subqueries are the only way to do this (correct?). I found the postgres documentation on how to handle json, so I started to loop over the objects:

SELECT jsonb_array_elements(details)
FROM table;

That simply gives me a list of the objects. So I now need to filter by up for which I think I need to use json_to_recordset(). I tried this:

SELECT *
FROM json_to_recordset('[{"direction":"up", "result": 1.0}, {"direction":"up", "result": 2.0}, {"direction":"down", "result": 3.0}]') as x(direction varchar, result float)
WHERE direction = 'up';

which results in the expected

direction | result
------------------
up        | 1
up        | 2

Now let's sum them:

SELECT SUM(result) as up_sum
FROM json_to_recordset('[{"direction":"up", "result": 1.0}, {"direction":"up", "result": 2.0}, {"direction":"down", "result": 3.0}]') as x(direction varchar, result float)
WHERE direction = 'up';

Great, that works!

Now I inserted that into my previous query:

SELECT 
    jsonb_array_elements(details),
    (
        SELECT SUM(result)
        FROM json_to_recordset('[{"direction":"up", "result": 1.0}, {"direction":"up", "result": 2.0}, {"direction":"down", "result": 3.0}]') as x(direction varchar, result float)
        WHERE direction = 'up'
    ) as up_sum
FROM table;

Ok, that also works great.

Now I just need to use the result of jsonb_array_elements(details) in the json_to_recordset() (or actualy jsonb_to_recordset() for the jsonb field). So I then ran this:

SELECT 
    jsonb_array_elements(details),
    (
        SELECT SUM(result)
        FROM jsonb_to_recordset(jsonb_array_elements(details)) as x(direction varchar, result float)
        WHERE direction = 'up'
    ) as up_sum
FROM table;

Unfortunately that gives an error:

ERROR: set-returning functions must appear at top level of FROM

Could anybody hint me in the right direction?

Your "inserted that into my previous" query does not work. It there a comma missing? dbfiddle.uk/… — S-Man
– S-Man, Commented Sep 24, 2019 at 10:01

S-Man · Accepted Answer · 2019-09-24 11:19:11Z

6

The idea of your way is not quite clear to me. It seems very complicated.

But: The error you get: Because jsonb_array_elements() does not return just one single but many (a set of records, so, it is a "set-returning function"). A set of records cannot be used as an argument for another function directly. This is meant by "at the top level". Such a function can only appear directly as FROM list element.

Beside this: Here is the way I would chose to achieve your result:

demo:db<>fiddle

Getting only the up sums:

SELECT 
    sensor,
    SUM((elems ->> 'result')::numeric) AS up_sum    -- 3
FROM
    mytable,
    jsonb_array_elements(details) elems             -- 1
WHERE elems ->> 'direction' = 'up'                  -- 2
GROUP BY sensor

Expand the array elements into one row each
Filter these elements by the direction value
SUM the result values

If you want to get the sums of both direction, you could use the conditional aggregations using the FILTER clause:

SELECT 
    sensor,
    SUM((elems ->> 'result')::numeric)
        FILTER (WHERE elems ->> 'direction' = 'up') AS up_sum,
    SUM((elems ->> 'result')::numeric) 
        FILTER (WHERE elems ->> 'direction' = 'down') AS down_sum
FROM
    mytable,
    jsonb_array_elements(details) elems
GROUP BY sensor

answered Sep 24, 2019 at 11:19

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kramer65 Over a year ago

This is an awesome anwser. It took me some time to comprehend it, but I do now. One more question, I also want the 50th percentile of the up elements. I found the percentile_disc() function for that. But I'm not sure how to use it on elems. Any idea?

S-Man Over a year ago

I am not sure what result you are expecting (does this help in any way? dbfiddle.uk/…)

Ajay · Accepted Answer · 2019-09-24 12:19:30Z

Try the Below script

select a."sensor",(a."Result"->> 'direction')::character varying as "Direction",sum((a."Result"->> 'result')::float)::integer as "Result" from (
SELECT "sensor",jsonb_array_elements("details"::jsonb) "Result" FROM "A_Test1212"
) a
group by a."sensor",(a."Result"->> 'direction')::character varying 
order by a."sensor"

'A';'up';3
'A';'left';9
'A';'down';8
'B';'up';14
'B';'down';11

-- Pivot script

select * from crosstab(
'select a."sensor",(a."Result"->> ''direction'')::character varying as "Direction",sum((a."Result"->> ''result'')::float)::integer as "Result" from (
SELECT "sensor",jsonb_array_elements("details"::jsonb) "Result" FROM "A_Test1212"
) a
group by "sensor",(a."Result"->> ''direction'')::character varying
order by a."sensor"') as finalresult("sensor" character varying,"up" integer,"down" integer,"left" integer);


'A';3;9;8
'B';14;11;<NULL>

Collectives™ on Stack Overflow

PostgreSQL ERROR: set-returning functions must appear at top level of FROM

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related