Recursive query in Postgresql to create a menu tree

Question

How to create a json from a hierarchical SQL structure with Postgresql 12?

I need to create a menu as the image below. This menu is already working, but, the solution I am using is not very optimized and has a great overhead, since I am making many accesses to the database in PHP code with foreach and recursion.

I would like a solution to access the database only once and prepare the JSON tree.

I made an SQL code that is not working very well since it is not merging menu items from the same parent.

My current test implementation in SQL that is failing because it is repeating "Jubarte" and is not merging the items of "Jubarte"

CREATE TABLE menus
(
  id          bigserial NOT NULL PRIMARY KEY,
  customer_id integer   NOT NULL,
  "idPai"   bigint,
  label        text,
  rota text
);

INSERT INTO menus(customer_id, "idPai", label, rota) VALUES
(1,NULL,'Jubarte', ''),
(2,1,'Cadastros', ''),
(3,NULL,'Ciente', ''),
(4,1,'Autorizações', ''),
(5,4,'Menus', ''),
(6,2,'Organograma', ''),
(7,1,'Minha Conta', '');

WITH RECURSIVE menus_tree("id", "customer_id", "idPai", "label", "rota", "children") AS (
  -- tree leaves (no matching children)
  (SELECT c.*, json '[]'
  FROM menus c
  WHERE NOT EXISTS(SELECT * FROM menus AS hypothetic_child WHERE hypothetic_child."idPai" = c.id)   
   )
  UNION ALL
  -- pozs's awesome "little hack"
  SELECT (parent).*, json_agg(child) AS "children"
  FROM (
    SELECT parent, child
    FROM menus_tree AS child
    JOIN menus parent ON parent.id = child."idPai"
  ) branch
  GROUP BY branch.parent
)
SELECT jsonb_pretty(json_agg(t)::jsonb)
FROM menus_tree t
LEFT JOIN menus AS hypothetic_parent ON(hypothetic_parent.id = t."idPai")
WHERE hypothetic_parent.id IS NULL;
    

Result:

[
    {
        "id": 3,
        "rota": "",
        "idPai": null,
        "label": "Ciente",
        "children": [
        ],
        "customer_id": 3
    },
    {
        "id": 1,
        "rota": "",
        "idPai": null,
        "label": "Jubarte",
        "children": [
            {
                "id": 7,
                "rota": "",
                "idPai": 1,
                "label": "Minha Conta",
                "children": [
                ],
                "customer_id": 7
            }
        ],
        "customer_id": 1
    },
    {
        "id": 1,
        "rota": "",
        "idPai": null,
        "label": "Jubarte",
        "children": [
            {
                "id": 2,
                "rota": "",
                "idPai": 1,
                "label": "Cadastros",
                "children": [
                    {
                        "id": 6,
                        "rota": "",
                        "idPai": 2,
                        "label": "Organograma",
                        "children": [
                        ],
                        "customer_id": 6
                    }
                ],
                "customer_id": 2
            },
            {
                "id": 4,
                "rota": "",
                "idPai": 1,
                "label": "Autorizações",
                "children": [
                    {
                        "id": 5,
                        "rota": "",
                        "idPai": 4,
                        "label": "Menus",
                        "children": [
                        ],
                        "customer_id": 5
                    }
                ],
                "customer_id": 4
            }
        ],
        "customer_id": 1
    }
]

Answer 1

I have a solution, I don’t know if it’s the best, but it’s working like this, if anyone has a better idea, you can suggest

WITH RECURSIVE location_with_level AS (
    (
        SELECT
            menus.*,
            0 AS level 
        FROM
            menus,
            usuarios u, permissoes P -- <== part to filter by user permissions
            
        WHERE
            "idPai" IS NULL 
            -- part to filter by user permissions       
            AND u."idPerfil" = P."idPerfil" 
            AND u."idSistema" = P."idSistema" 
            AND P."idMenu" = menus."id" 
            AND u."idPessoa" = 2 
            AND menus."ativo" = 't'
            
        ) UNION ALL
        (
        SELECT
            child.*,
            parent.level + 1 
        FROM
            usuarios u, permissoes P, -- <== part to filter by user permissions
            menus child 
            JOIN location_with_level parent ON parent.ID = child."idPai" 
            -- part to filter by user permissions           
        WHERE
            u."idPerfil" = P."idPerfil" 
            AND u."idSistema" = P."idSistema" 
            AND P."idMenu" = child."id" 
            AND u."idPessoa" = 2 
            AND child."ativo" = 't'             
        ) 
    ),
    -- pega o nivel
    maxlvl AS ( SELECT MAX ( level ) maxlvl FROM location_with_level ),
    -- generates the JSON of the menu items for each system module
    c_tree AS (
        ( 
        SELECT location_with_level.*, NULL::JSONB nodes FROM location_with_level, maxlvl WHERE level = maxlvl ) 
        
        UNION
        (
            (
            SELECT
                ( branch_parent ).*,
                jsonb_agg ( branch_child order by (branch_child->>'ordem')::numeric) 
            FROM
                (
                SELECT
                    branch_parent,
                    to_jsonb ( branch_child )  /*- 'level' - 'idPai' - 'id'*/ AS branch_child 
                FROM
                    location_with_level branch_parent
                    JOIN c_tree branch_child ON branch_child."idPai" = branch_parent.ID                 
                    
                ) branch 
            GROUP BY
                branch.branch_parent 
            ) UNION
            (
            SELECT C
                .*,
                NULL :: JSONB 
            FROM
                location_with_level C 
            WHERE
                NOT EXISTS ( SELECT 1 FROM location_with_level hypothetical_child WHERE hypothetical_child."idPai" = C.ID ) 
            ) 
        
        ) 
    ) 
SELECT array_to_json(
           array_agg(
             row_to_json(c_tree)::JSONB /*- 'level' - 'idPai' - 'id'*/ order by sistemas.ordem
           )
         )::JSONB
        AS tree             
  FROM c_tree
    JOIN sistemas on c_tree."idSistema" = sistemas.id
  WHERE level=0