I've been experimenting with PostgreSQL's Ltree extension. I'd like to use it to store hierarchical to-do list data (i.e. lists with sub lists). It works well, but after spending a fair bit of time, I still can't find a nice way to retrieve the information from the database in hierarchical JSON format. Here is an example of what I'd like to achieve:
id | content | position | parent_id | parent_path
----+------------------------+-----------+----------+-------------+
1 | Fix lecture notes. | 1 | | root
2 | Sort out red folder. | 1 | 1 | root.1
3 | Order files. | 1 | 2 | root.1.2
4 | Label topics. | 2 | 2 | root.1.2
5 | Sort out blue folder. | 2 | 1 | root.1
6 | Look for jobs. | 2 | | root
From this, to the json output below:
[
{
"id":1,
"content":"Fix lecture notes.",
"position":1,
"parent_id":null,
"parent_path":"root",
"children":[
{
"id":2,
"content":"Sort out red folder.",
"position":1,
"parent_id":1,
"parent_path":"root.1",
"children":[
{
"id":3,
"content":"Order files.",
"position":1,
"parent_id":2,
"parent_path":"root.1.2",
"children":[]
},
{
"id":4,
"content":"Label topics.",
"position":2,
"parent_id":2,
"parent_path":"root.1.2",
"children":[]
}
]
},
{
"id":2,
"content":"Sort out blue folder.",
"position":2,
"parent_id":1,
"parent_path":"root.1",
"children":[]
}
]
},
{
"id":1,
"content":"Look for jobs.",
"position":1,
"parent_id":null,
"parent_path":"root",
"children":[]
}
]
Is there any neat way this can be done, maybe with Python server side? Looking for ideas really!
