sql - PostgreSQL错误:设置返回函数必须出现在FROM的顶层

所以我有一个postgres数据库，其中有一个名为jsonb的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}]

我现在需要按传感器分组的所有向上记录和向下记录的result的总和。
所以我的查询结果应该是：

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

我需要某种方式：
循环遍历details数组对象
按up和down筛选
然后将每个传感器的记录结果相加
我想子查询是唯一的方法（正确吗？）。我找到了the postgres documentation on how to handle json，所以我开始在对象上循环：

SELECT jsonb_array_elements(details)
FROM table;

这只是给了我一个对象列表。所以我现在需要通过up进行过滤，我认为我需要使用json_to_recordset()。我试过这个：

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';

结果是

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

现在我们来总结一下：

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';

太好了，真管用！
现在我将它插入到上一个查询中：

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;

好吧，那也很好。
现在我只需要在jsonb_array_elements(details)中使用json_to_recordset()的结果（或者在jsonb_to_recordset()字段中实际使用jsonb）。然后我运行这个：

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;

不幸的是，这给出了一个错误：
错误：集合返回函数必须出现在FROM的顶层
有人能告诉我正确的方向吗？

最佳答案

你的路我不太清楚。看起来很复杂。
但是：您得到的错误是：因为jsonb_array_elements()不返回一个而是多个（一组记录，因此，它是一个“set returning函数”）。一组记录不能直接用作另一个函数的参数。意思是“在最高层”。这样的函数只能直接显示为FROMlist元素。
除此之外：以下是我选择的实现您的结果的方法：
demo:db<>fiddle
仅获取up和：

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

将数组元素分别展开为一行
按direction值过滤这些元素
对result值求和
如果要获得两个方向的和，可以使用FILTER子句使用条件聚合：

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

关于sql - PostgreSQL错误:设置返回函数必须出现在FROM的顶层，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58077307/