CODEWITHSUNDEEP
sqlpostgresql
kramer65
kramer65

Reputation: 53873

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

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:

  1. loop over the details array objects
  2. filter by up and down
  3. 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?

Upvotes: 4

Views: 7665

Answers (2)

Ajay
Ajay

Reputation: 774

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>

Upvotes: 0

S-Man
S-Man

Reputation: 23676

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
  1. Expand the array elements into one row each
  2. Filter these elements by the direction value
  3. 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

Upvotes: 6

Related Questions