I have this materialized path tree structure built using PostgreSQL's ltree module.
- id1
- id1.id2
- id1.id2.id3
- id1.id2.id5
- id1.id2.id3.id4 ... etc
I can of course easily use ltree to get all nodes from the entire tree or from a specific path/subpath, but when I do that, naturally what I get is a lot of rows (which equals to an array/slice of nodes in the end.. Golang/whatever programming language you use)
What I'm after is to fetch the tree - ideally from a certain start and ending path/point - as a hieracical JSON tree object like etc
{
"id": 1,
"path": "1",
"name": "root",
"children": [
{
"id": 2,
"path": "1.2",
"name": "Node 2",
"children": [
{
"id": 3,
"path": "1.2.3",
"name": "Node 3",
"children": [
{
"id": 4,
"path": "1.2.3.4",
"name": "Node 4",
"children": [
]
}
]
},
{
"id": 5,
"path": "1.2.5",
"name": "Node 5",
"children": [
]
}
]
}
]
}
I know from a linear (non-hiearchical) row/array/slice resultset I can of course in Golang explode the path and make the necessary business logic there to create this json, but it'll certainly be MUCH much better if there's a handy way of achieving this with PostgreSQL directly.
So how would you in PostgreSQL output an ltree tree structure to json - potentionally from a starting to ending path?
If you don't know ltree, I guess the question could be generalized more to "Materalized path tree to hierachical json"
Also I'm playing with the thought of adding a parent_id on all nodes in addition to the ltree path, since at least then I would be able to use recursive calls using that id to fetch the json I guess... also I've thought about putting a trigger on that parent_id to manage the path (keep it updated) based on when a change in parent id happens - I know it's another question, but perhaps you could tell me your opinion as well, about this?
I hope some genius can help me with this. :)
For your convenience here's a sample create script you can use to save time:
CREATE TABLE node
(
id bigserial NOT NULL,
path ltree NOT NULL,
name character varying(255),
CONSTRAINT node_pkey PRIMARY KEY (id)
);
INSERT INTO node (path,name)
VALUES ('1','root');
INSERT INTO node (path,name)
VALUES ('1.2','Node 1');
INSERT INTO node (path,name)
VALUES ('1.2.3','Node 3');
INSERT INTO node (path,name)
VALUES ('1.2.3.4','Node 4');
INSERT INTO node (path,name)
VALUES ('1.2.5','Node 5');
I was able to find and slightly change it to work with ltree's materialized paths instead of parent ids like often used on adjacency tree structures.
While I still hope for a better solution, this I guess will get the job done.
I kinda feel I have to add the parent_id in addition to the ltree path, since this is of course not any way near as fast as referencing parent id's.
Well credits goes to this guy's solution, and here's my slightly modified code using ltree's subpath, ltree2text and nlevel to achieve the exact same:
There is a big problem with this solution though, so far.. see the image below for the error (Node 5 is missing):