Nesting json_agg() inside json_agg()

Question

I have these 2 tables tasks and subtasks with these columns:

Tasks
id
name
description
Subtasks
id
name
description
task_id

I'm trying to do a query that would output something like this:

[
{
id:30,
name:"Do something",
description:"Do this",
  sub_task:[
    {id:1,name:"test",description:"test"}
  ]
}
]

After some research I found out how to query data as json and it works if I don't have something nested, and I can't figure out how to do it. This is what I tried:

let query =
      "SELECT t.id, json_agg(json_build_object('name',t.name,'description',t.description,'id',t.id,'subtask',json_agg(json_builder_object('name',s.name,'description',s.description)))) as test from task t ";
    query += " inner join subtask s on s.task_id = t.id ";

I tried to do this nested json_agg() but I get this error:

json_builder_object(unknown, character varying, unknown, text) does not exist

How should I do it?

Erwin Brandstetter · Answer 1 · 2022-05-05T02:15:08.007

Well, it's json_build_object(), not ~~json_builder_object()~~.

But the main issue is that you cannot nest aggregate functions. You need a subquery or a CTE for that. Like:

SELECT json_agg(json_build_object('id',t.id, 'name',t.name, 'description',t.description, 'subtask',s.sub_task)) AS task
FROM   tasks t
CROSS  JOIN LATERAL (
   SELECT json_agg(json_build_object('id',s.id, 'name',s.name, 'description',s.description)) AS sub_task
   FROM   subtasks s
   WHERE  task_id = t.id
   ) s;

Or equivalent:

SELECT json_agg(t) AS tasks
FROM  (
   SELECT *
   FROM   tasks t
   CROSS  JOIN LATERAL (
      SELECT json_agg(sub_task) AS sub_task
      FROM  (
         SELECT id, name, description
         FROM   subtasks
         WHERE  task_id = t.id
         ) sub_task
      ) s
   ) t;

Wow, great answer! Thank you https://stackoverflow.com/users/939860/erwin-brandstetter! — Yoni Rabinovitch, Nov 26 '21 at 11:00

Nesting json_agg() inside json_agg()

1 Answers1