How do I write an UPDATE statement for a JSON column using a nested aggregation?

I have two tables, jobs and job_statuses. Jobs has a rows with a group and status column and job_statuses has a statuses JSON column that contains a count of statuses for that group:

jobs

id	group	name	status
1	2	foo	running
1	2	bar	done

How do I write an UPDATE statement for a JSON column using a nested aggregation?

I have two tables, jobs and job_statuses. Jobs has a rows with a group and status column and job_statuses has a statuses JSON column that contains a count of statuses for that group:

jobs

id	group	name	status
1	2	foo	running
1	2	bar	done

job_statuses

group	statuses
2	{"running": 1, "done": 1}

I want to be able to write an UPDATE to set statuses to for a given group. That is, update only one row at a time.

So far I've tried this with a CTE but I'm unsure of how to write the json_object_agg without getting a aggregate functions are not allowed in UPDATE error.

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;

What is the correct way to write this UPDATE?

---

Solution

As @Zegarek points out. I can just use a second CTE to remove the aggregation in the UPDATE statement.

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_build_object(status, count) as result FROM status_agg
) 
UPDATE pipeline_stat
SET statuses = json_status.result
FROM json_status
WHERE pipeline_stat.id = 2;

• postgresql
• common-table-expression

Share Improve this question Follow edited Mar 22 at 17:29 Aage Torleif asked Mar 22 at 17:09 Aage TorleifAage Torleif 2,02511 gold badge2222 silver badges4242 bronze badges 2

• 1 You can add another CTE that does the second aggregation. – Zegarek Commented Mar 22 at 17:17
• Oh? Yes this seems to work. Thank you, very simple solution. – Aage Torleif Commented Mar 22 at 17:27

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

update job_statuses as target
set statuses=(select json_object_agg(status,count)
              from(select job.status as status
                        , count(job.id) as count 
                   from jobs as job
                   where job.group_ = target.group_
                   group by job.status
                   order by job.status) as s1)
where group_ = 2;

A scalar subquery above is another way you can handle this. Adding a CTE works too:
_{demo at db<>fiddle}

with status_agg as (
  select job.status as status
       , count(job.id) as count 
  from jobs as job
  where job.group_ = 2
  group by job.status
  order by job.status
),status_agg_jsonb as(
  select json_object_agg(  status_agg.status
                         , status_agg.count) as v
  from status_agg)
update job_statuses
set statuses = status_agg_jsonb.v
from status_agg_jsonb
where job_statuses.group_ = 2;

Instead of plain json, consider jsonb.