Get bottom-up nested json for query in postgresql

demo:db<>fiddle

(Explanation below)

WITH RECURSIVE hierarchy AS (    SELECT id, parent_id    FROM categories     WHERE id = 999        UNION        SELECT        c.id, c.parent_id    FROM categories c    JOIN hierarchy h ON h.parent_id = c.id),jsonbuilder AS (    SELECT         c.id,         h.parent_id,        jsonb_build_object('id', c.id, 'name', c.name, 'description', c.description, 'slug', c.slug) as jsondata    FROM hierarchy h    JOIN categories c ON c.id = h.id    WHERE h.parent_id IS NULL        UNION        SELECT        c.id,        h.parent_id,        jsonb_build_object('id', c.id, 'name', c.name, 'description', c.description, 'slug', c.slug, 'parent', j.jsondata)      FROM hierarchy h    JOIN categories c ON c.id = h.id    JOIN jsonbuilder j ON j.id = h.parent_id)SELECT     jsondataFROM jsonbuilderWHERE id = 999

Generally you need a recursive query to create nested JSON objects. The naive approach is:

1. Get record with id = 999, create a JSON object
2. Get record with id = parent_id of record with 999 (id = 2), build JSON object, add this als parent attribute to previous object.
3. Repeat step 2 until parent is NULL

Unfortunately I saw no simple way to add a nested parent. Each step nests the JSON into deep. Yes, I am sure, there is a way to do this, storing a path of parents and use jsonb_set() everytime. This could work.

On the other hand, it's much simpler to put the currently created JSON object into a new one. So to speak, the approach is to build the JSON from the deepest level. In order to do this, you need the parent path as well. But instead create and store it while creating the JSON object, you could create it first with a separate recursive query:

WITH RECURSIVE hierarchy AS (    SELECT id, parent_id    FROM categories     WHERE id = 999        UNION        SELECT        c.id, c.parent_id    FROM categories c    JOIN hierarchy h ON h.parent_id = c.id)SELECT * FROM hierarchy

Fetching the record with id = 999 and its parent. Afterwards fetch the record of the parent, its id and its parent_id. Do this until parent_id is NULL.

This yields:

 id | parent_id--: | --------:999 |         2  2 |         1  1 |      null

Now we have a simple mapping list which shows the traversal tree. What is the difference to our original data? If your data contained two or more children for record with id = 1, we would not know which child we have to take to finally reach child 999. However, this result lists exactly only the anchestor relations and would not return any siblings.

Well having this, we are able to traverse the tree from the topmost element which can be embedded at the deepest level:

1. Fetch the record which has no parent. Create a JSON object from its data.
2. Fetch the child of the previous record. Create a JSON object from its data and embed the previous JSON data as parent.
3. Continue until there is no child.

How does it work?

This query uses recursive CTEs. The first part is the initial query, the first record, so to speak. The second part, the part after UNION, is the recursive part which usually references to the WITH clause itself. This is always a reference to the previous turn.

The JSON part is simply creating a JSON object using jsonb_build_object() which takes an arbitrary number of values. So we can use the current record data and additionally for the parent attribute the already created JSON data from the previous turn.