Creates a nested json structure from hierarchical data

tree.sql

with recursive categories_from_parents as
(
         -- Classes with no parent, our starting point
      select id, name, '{}'::int[] as parents, 0 as level
        from categories
       where parent_id is NULL

   union all

         -- Recursively find sub-classes and append them to the result-set
      select c.id, c.name, parents || c.parent_id, p.level+1
        from      categories_from_parents p
             join categories c
               on c.parent_id = p.id
       where not c.id = any(parents)
),
    categories_from_children as
(
         -- Now start from the leaf nodes and recurse to the top-level
         -- Leaf nodes are not parents (level > 0) and have no other row
         -- pointing to them as their parents, directly or indirectly
         -- (not id = any(parents))
     select c.parent_id,
            json_agg(jsonb_build_object('id', id) || jsonb_build_object('name', c.name))::jsonb as js
       from categories_from_parents tree
            join categories c using(id)
      where c.level > 0 and not id = any(parents)
   group by c.parent_id

  union all

         -- build our JSON document, one piece at a time
         -- as we're traversing our graph from the leaf nodes,
         -- the bottom-up traversal makes it possible to accumulate
         -- sub-classes as JSON document parts that we glue together
     select c.parent_id,
            jsonb_build_object('id', c.id) ||
            jsonb_build_object('name', c.name)
            || jsonb_build_object('children', js) as js

       from categories_from_children tree
            join categories c on c.id = tree.parent_id
)
-- Finally, the traversal being done, we can aggregate
-- the top-level classes all into the same JSON document,
-- an array.
select jsonb_pretty(jsonb_agg(js))
  from categories_from_children
 where parent_id IS NULL;