recursive ltree postgres

recursive ltree postgres.sql

WITH RECURSIVE c AS (
    SELECT id, name, username, sponsor, parent_id, root_node_path, 0 as lvl
    FROM users
    WHERE id = 1 and root_node_path is not null
  UNION ALL
    SELECT users.id, users.name, users.username, users.sponsor, users.parent_id, users.root_node_path, c.lvl + 1 as lvl
    FROM users 
    JOIN c ON ltree2text(subpath(users.root_node_path,nlevel(users.root_node_path)-2 ,nlevel(users.root_node_path))) = CONCAT(subpath(c.root_node_path,nlevel(c.root_node_path)-1,nlevel(c.root_node_path)),'.',users.id)
),
maxlvl AS (
  SELECT max(lvl) maxlvl FROM c
),
j AS (
    SELECT c.*, json '[]' children
    FROM c, maxlvl
    WHERE lvl = maxlvl
  UNION ALL
    SELECT (c).*, array_to_json(array_agg(j) || array(SELECT r
                                                        FROM   (SELECT l.*, json '[]' children
                                                                FROM   c l, maxlvl
                                                                where ltree2text(subpath(l.root_node_path,nlevel(l.root_node_path)-2,nlevel(l.root_node_path))) = CONCAT(subpath((c).root_node_path,nlevel((c).root_node_path)-1,nlevel((c).root_node_path)),'.',l.id)
                                                                AND    l.lvl < maxlvl
                                                                AND    NOT EXISTS (SELECT 1
                                                                                   FROM   c lp
                                                                                   WHERE  ltree2text(subpath(lp.root_node_path,nlevel(lp.root_node_path)-2,nlevel(lp.root_node_path))) = CONCAT(subpath(l.root_node_path,nlevel(l.root_node_path)-1,nlevel(l.root_node_path)),'.',lp.id))) r)) children
   FROM (SELECT c, j
      	 FROM j
      	 JOIN c ON ltree2text(subpath(j.root_node_path,nlevel(j.root_node_path)-2,nlevel(j.root_node_path))) = CONCAT(subpath(c.root_node_path,nlevel(c.root_node_path)-1,nlevel(c.root_node_path)),'.',j.id)
    ) v
    GROUP BY v.c
)
SELECT row_to_json(j) tree
FROM j
WHERE lvl = 0;