Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE items (
  item_id  serial PRIMARY KEY
, title    text NOT NULL
);

CREATE TABLE joins (
  id       serial PRIMARY KEY
, item_id  int NOT NULL REFERENCES items
, child_id int NOT NULL REFERENCES items
);

INSERT INTO items (item_id,title) VALUES
  (1,'PARENT'),
  (2,'LEVEL 2'),
  (3,'LEVEL 3.1'),
  (4,'LEVEL 4.1'),
  (5,'LEVEL 4.2'),
  (6,'LEVEL 3.2');
  
INSERT INTO joins (item_id, child_id) VALUES
  (1,2),
  (2,3),
  (3,4),
  (3,5),
  (2,6);

CREATE TABLE



CREATE TABLE



INSERT 0 6



INSERT 0 5


CREATE OR REPLACE FUNCTION f_item_tree(_item_id int, _level int = 7)
  RETURNS jsonb
  LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT CASE WHEN _level > 1
            THEN jsonb_agg(sub)
            ELSE CASE WHEN count(*) > 0 THEN to_jsonb(count(*) || ' - stopped recursion at max level') END
       END
FROM  (
   SELECT i.*, CASE WHEN _level > 1 THEN f_item_tree(i.item_id, _level - 1) END AS children
   FROM   joins j
   JOIN   items i ON i.item_id = j.child_id
   WHERE  j.item_id = _item_id
   ORDER  BY i.item_id
   ) sub
$func$;

CREATE FUNCTION


-- Simple call (defaults to max 7 levels):
SELECT jsonb_pretty(jsonb_strip_nulls(to_jsonb(sub))) AS tree
FROM  (
   SELECT *, f_item_tree(item_id) AS children
   FROM   items
   WHERE  item_id = 1  -- root item_id HERE
   ) sub;


tree


{
    "title": "PARENT",
    "item_id": 1,
    "children": [
        {
            "title": "LEVEL 2",
            "item_id": 2,
            "children": [
                {
                    "title": "LEVEL 3.1",
                    "item_id": 3,
                    "children": [
                        {
                            "title": "LEVEL 4.1",
                            "item_id": 4
                        },
                        {
                            "title": "LEVEL 4.2",
                            "item_id": 5
                        }
                    ]
                },
                {
                    "title": "LEVEL 3.2",
                    "item_id": 6
                }
            ]
        }
    ]
}



SELECT 1


-- Call with just 3 levels:
SELECT jsonb_pretty(jsonb_strip_nulls(to_jsonb(sub))) AS tree
FROM  (
   SELECT *, f_item_tree(item_id, 3) AS children
   FROM   items
   WHERE  item_id = 1  -- root item_id HERE
   ) sub;


tree


{
    "title": "PARENT",
    "item_id": 1,
    "children": [
        {
            "title": "LEVEL 2",
            "item_id": 2,
            "children": [
                {
                    "title": "LEVEL 3.1",
                    "item_id": 3,
                    "children": "2 - stopped recursion at max level"
                },
                {
                    "title": "LEVEL 3.2",
                    "item_id": 6
                }
            ]
        }
    ]
}



SELECT 1


-- Alternative: iterative aggregation
-- Max 4 levels deep
WITH RECURSIVE path AS (
   SELECT ARRAY[item_id, child_id] AS path, child_id AS item_id, 2 AS lvl
   FROM   joins
   WHERE  item_id = 1  -- root item_id HERE
   
   UNION ALL
   SELECT p.path || j.child_id, j.child_id, p.lvl + 1
   FROM   path  p
   JOIN   joins j ON j.item_id = p.item_id
   WHERE  p.lvl < 7  -- HARD limit
   )
, tree AS (
   SELECT p.*, to_jsonb(i) AS item
   FROM   path p
   JOIN   items i USING (item_id)
   ORDER  BY lvl, item_id
   )
, lvl4 AS (  -- leaf!
   SELECT path[3] AS item_id, jsonb_agg(item) AS children
   FROM   tree
   WHERE  lvl = 4
   GROUP  BY 1
   )
,  lvl3 AS (
   SELECT path[2] AS item_id
        , jsonb_agg(jsonb_set_lax(item, '{children}', children, true, 'return_target')) AS children
   FROM   tree t
   LEFT   JOIN lvl4 USING (item_id)
   WHERE  lvl = 3
   GROUP  BY 1
   )
,  lvl2 AS ( -- stem!
   SELECT jsonb_agg(jsonb_set_lax(item, '{children}', children, true, 'return_target')) AS children
   FROM   tree t
   LEFT   JOIN lvl3 USING (item_id)
   WHERE  lvl = 2
   )
SELECT i.item_id
     , jsonb_pretty(jsonb_set_lax(to_jsonb(i), '{children}', children, true, 'return_target')) AS tree
FROM   items i
LEFT   JOIN lvl2 ON true
WHERE  item_id = 1;  -- root item_id HERE


item_id
tree


1
{
    "title": "PARENT",
    "item_id": 1,
    "children": [
        {
            "title": "LEVEL 2",
            "item_id": 2,
            "children": [
                {
                    "title": "LEVEL 3.1",
                    "item_id": 3,
                    "children": [
                        {
                            "title": "LEVEL 4.1",
                            "item_id": 4
                        },
                        {
                            "title": "LEVEL 4.2",
                            "item_id": 5
                        }
                    ]
                },
                {
                    "title": "LEVEL 3.2",
                    "item_id": 6
                }
            ]
        }
    ]
}



SELECT 1