Get bottom-up nested json for query in postgresql

Question

Given the following table, I want to find a category by ID, then get a JSON object containing its parent row as JSON. if I look up category ID 999 I would like the following json structure.

How can I achieve this?

{
  id: 999
  name: "Sprinting",
  slug: "sprinting",
  description: "sprinting is fast running",
  parent: {
    id: 2
    name: "Running",
    slug: "running ",
    description: "All plans related to running.",
    parent: {
      id: 1
      name: "Sport",
      slug: "sport ",
      description: null,
    }
  }
}

CREATE TABLE public.categories (
    id integer NOT NULL,
    name text NOT NULL,
    description text,
    slug text NOT NULL,
    parent_id integer
);

INSERT INTO public.categories (id, name, description, slug, parent_id) VALUES (1, 'Sport', NULL, 'sport', NULL);
INSERT INTO public.categories (id, name, description, slug, parent_id) VALUES (2, 'Running', 'All plans related to running.', 'running', 1);
INSERT INTO public.categories (id, name, description, slug, parent_id) VALUES (999, 'Sprinting', 'sprinting is fast running', 'sprinting', 2);```

Answer 1

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 
    jsondata
FROM jsonbuilder
WHERE 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.