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

Question

所以我有一个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

我需要某种方式：

1. 遍历详细信息数组对象
2. 用up和过滤down
3. 然后将每个传感器的记录结果相加

我猜子查询是执行此操作的唯一方法（正确吗？）。我找到了有关如何处理json的postgres文档，因此我开始遍历对象：

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()（或actualyjsonb_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的顶层

有人可以向我暗示正确的方向吗？

Answer 1

你的想法对我来说不是很清楚。看来很复杂。

但是：您得到的错误：因为jsonb_array_elements()不仅返回一个而是多个（一组记录，所以它是一个“返回集合的函数”）。一组记录不能直接用作另一个函数的参数。这就是“最高层”的意思。这样的功能只能直接显示为FROM列表元素。

除此之外：这是我选择实现您的结果的方式：

演示：db
<>小提琴

仅获取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

1. 将数组元素展开为每一行
2. 按direction值过滤这些元素
3. 将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