I'm trying to create a storage system for custom categories using postgres.

After looking around for potential solutions I settled on trying to use ltree;

Here is an example of raw data below;

+----+---------+---------------------------------+-----------+ | id | user_id | path | name | +----+---------+---------------------------------+-----------+ | 1 | 1 | root.test | test | | 2 | 1 | root.test.inbox | inbox | | 3 | 1 | root.personal | personal | | 4 | 1 | root.project | project | | 5 | 1 | root.project.idea | idea | | 6 | 1 | root.personal.events | events | | 7 | 1 | root.personal.events.janaury | january | | 8 | 1 | root.project.objective | objective | | 9 | 1 | root.personal.events.february | february | | 10 | 1 | root.project.objective.january | january | | 11 | 1 | root.project.objective.february | february | +----+---------+---------------------------------+-----------+

I thought that it might be easier to first order the results, and remove the top level from the path return. Using;

select id, name, subpath(path, 1) as path, nlevel(subpath(path, 1)) as level from testLtree order by level, path

I get;

+----+-----------+----------------------------+-------+ | id | name | path | level | +----+-----------+----------------------------+-------+ | 3 | personal | personal | 1 | | 4 | project | project | 1 | | 1 | test | test | 1 | | 6 | events | personal.events | 2 | | 5 | idea | project.idea | 2 | | 8 | objective | project.objective | 2 | | 2 | inbox | test.inbox | 2 | | 9 | february | personal.events.february | 3 | | 7 | january | personal.events.january | 3 | | 11 | february | project.objective.february | 3 | | 10 | january | project.objective.january | 3 | +----+-----------+----------------------------+-------+

I'm hoping to be able to transform this result into a set of JSON data somehow. I would like an output similar to this;

personal: { id: 3, name: 'personal', children: { events: { id: 6, name: 'events', children: { january: { id: 7, name: 'january', children: null }, february: { id: 9, name: 'february', children: null } } } } }, project: { id: 4, name: 'project', children: { idea: { id: 5, name: 'idea', children: null }, objective: { id: 8, name: 'objective', children: { january: { id: 10, name: 'january', children: null }, february: { id: 11, name: 'february', children: null } } } }] }, test: { id: 1, name: 'test', children: { inbox: { id: 2, name: 'inbox', children: null } } }

I've been looking around for the best way to do this but haven't came across any solutions that make sense to me. However, as I am new to postgres and SQL in general this is expected.

I think I may have to use a recursive query? I'm a bit confused over what the best method/execution of this would be. Any help/advice is much appreciated! and any further questions please ask.

I've put everything into a sqlfiddle below;

http://sqlfiddle.com/#!17/1713e/5