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)