如何编写含嵌套聚合的JSON列UPDATE语句？

我有两张表：jobs和job_statuses。jobs表包含group、status等列，job_statuses表有一个statuses JSON列，用于存储对应group的状态统计：

jobs表 #

job_statuses表 #

我希望编写一条UPDATE语句，为指定的group更新statuses列，每次仅更新一行。

目前我尝试用CTE实现，但不知道如何正确编写json_object_agg，总是遇到“aggregate functions are not allowed in UPDATE”错误。

尝试的SQL代码：

WITH status_agg AS (
  SELECT
    job.status as status,
    count(job.id) AS count 
  FROM
    job
  WHERE
    job.group = 2
  GROUP BY
    job.status
  ORDER BY
    job.status
)
UPDATE job_statuses
SET statuses = json_build_object(status_agg.status, status_agg.count)
FROM status_agg
WHERE job_statuses.group = 2;

请问这条UPDATE语句的正确写法是什么？

解决方案 #

可以通过新增一个CTE来将聚合后的状态统计转换成完整的JSON对象，避免在UPDATE语句中直接使用聚合函数，正确写法如下：

WITH status_agg AS (
  SELECT
    platform.task.status as status,
    count(platform.task.id) AS count 
  FROM
    platform.task 
  WHERE
    platform.task.pipeline_id = 2
  GROUP BY
    platform.task.status
  ORDER BY
    platform.task.status
),
json_status AS (
  SELECT json_object_agg(status, count) as result FROM status_agg
) 
UPDATE pipeline_stat
SET statuses = json_status.result
FROM json_status
WHERE pipeline_stat.id = 2;

说明：原尝试的代码中，直接在UPDATE的SET子句中使用json_build_object会因为关联多行数据导致更新异常，新增的json_status CTE通过json_object_agg将所有状态-计数对聚合为一个完整的JSON对象，确保UPDATE语句仅执行一次，得到符合需求的统计结果。

内容的提问来源于Stack Exchange，提问作者Aage Torleif