Use ltreee + plv8 to fetch hirarcical records as json

This tip is a coninuation of working with hierarcical data in postgres

First we need some extensions:

CREATE EXTENSION ltree;
CREATE EXTENSION plv8;

The basic structure for our relationships will make use of a parent_id field for referencing ourself, a parent_path field to hold the position within the hierarchy and a position field to order the records within each level. For details on why we are using both a parent_id AND a parent_path see this tip

CREATE TABLE section (
    id SERIAL PRIMARY KEY,
    parent_id INTEGER,
    parent_path LTREE,
    position INTEGER
);

Add a GIST index to improve <@ and @> queries (among others).

CREATE INDEX path_gist_idx ON questions USING GIST (path);

CREATE OR REPLACE FUNCTION json_child_tree(parent text) RETURNS json AS $js$
    var rows = plv8.execute('SELECT * FROM section WHERE parent_path <@ $1 ORDER BY nlevel(parent_path),position', [parent]);
    var all = {},
        out = [],
        top,r,i;
    for(i=0; i<rows.length; i++){
        r = rows[i];
        r.children = [];
        all[r.id] = r;
        if(r.parent_path == parent){
            out.push(r);
        }
    }
    for(i=0; i<rows.length; i++){
        r = rows[i];
        if(all[ r.parent_id ]){
            all[ r.parent_id ].children.push(r);
        }
    }
    return JSON.stringify(out,null,4);
$js$ LANGUAGE plv8;

Try fetching the whole tree:

psql=# SELECT json_child_tree('root');
                     json_child_tree                      
----------------------------------------------------
 [                                                 +
     {                                             +
         "id": 1,                                  +
         "name": "Section A",                      +
         "parent_id": null,                        +
         "parent_path": "root",                    +
         "position": null,                         +
         "children": [                             +
             {                                     +
                 "id": 2,                          +
                 "name": "Section A.1",            +
                 "parent_id": 1,                   +
                 "parent_path": "root.1",          +
                 "position": null,                 +
                 "children": [                     +
                     {                             +
                         "id": 5,                  +
                         "name": "Section A.1.1",  +
                         "parent_id": 2,           +
                         "parent_path": "root.1.2",+
                         "position": null,         +
                         "children": []            +
                     }                             +
                 ]                                 +
             }                                     +
         ]                                         +
     }                                             +
 ]
(1 row)