0
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
}
]
Hello! Put this HTML menu in the body of the question, avoid image.
– Fabiano Monteiro
Well, the image is just an example to illustrate what I’m doing, it has nothing to do with the heart of the matter
– Isaque Neves