sql - 在 PostgreSQL 的 array_to_json(array_agg(row_to_json())) 中嵌套一个 select 语句

Question

我试图理解为什么会这样:

select
    array_to_json(array_agg(row_to_json(positions_table)))
from (
    select
        sum(start_month),
        function_id,
        profile_id
    from positions as subquery
    group by function_id, profile_id
) as positions_table

但这不是:

select
    profiles.id,
    (array_to_json(array_agg(row_to_json(
        select
            sum(start_month),
            function_id,
            profile_id
        from positions as subquery
        group by function_id, profile_id
    ))))
from profiles

似乎不允许我将 select... 语句放在 array_to_json(array_agg(row_to_json())) 中，它需要引用一个表格代替。

但我怀疑我可能遗漏了什么。

错误是select 附近的语法错误。

Answer 1

您可以通过将子选择括在括号中来修复语法错误，但是您会看到第二个错误； postgres 会报告:

subquery must return only one column

如果您修复了那个错误，您会看到:

column "profiles.id" must appear in the GROUP BY clause or be used in an aggregate function

...这越来越接近问题的真正原因。添加 GROUP BY id 将使 postgres 报告根本原因:

more than one row returned by a subquery used as an expression

row_to_json 函数需要一行。

要按配置文件 ID 聚合您的 json 摘要，您必须执行连接。

SELECT
  pr.id,
  array_to_json(array_agg(row_to_json(positions_table.*)))
FROM profiles pr JOIN (
  SELECT sum(start_month), function_id, profile_id
  FROM positions
  GROUP BY function_id, profile_id
) positions_table ON positions_table.profile_id = pr.id
GROUP BY pr.id

(您可以从 positions_table.* 中删除 .*，我只是为了清楚起见才添加它)。