PostgreSQL - Generate JSON-tree-object from table containing paths

Seems like you have a list of paths, where parts overlap.

Update 2024

With jsonb and jsonb_object_agg(), producing a
denser result where each edge is represented one key/value pair.

jsonb removes duplicate key values out of the box. No DISTINCT required:

SELECT jsonb_object_agg(node, parent) AS array_of_edges
FROM  (
   SELECT node, lag(node) OVER (PARTITION BY path_id ORDER BY id) AS parent
   FROM   tbl
   ORDER  BY parent NULLS FIRST, node  -- ORDER BY optional
   ) sub;

jsonb_object_agg_strict() also removes objects with null values, effectively trimming the dangling edge at the root:

SELECT jsonb_object_agg_strict(node, parent) AS edges
FROM  (
   SELECT node, lag(node) OVER (PARTITION BY path_id ORDER BY id) AS parent
   FROM   tbl
   ORDER  BY parent NULLS FIRST, node  -- ORDER BY optional
   ) sub;

Original answer 2014

Legacy json solution. First remove duplicate edges, as json keeps all objects, even duplicate keys.

SELECT DISTINCT
       node, lag(node) OVER (PARTITION BY path_id ORDER BY id) AS parent
FROM   tbl
ORDER  BY parent NULLS FIRST, node;  -- ORDER BY optional

parent is NULL for the root node. You may want to remove this "non-edge" from the result.
Then, to "generate a JSON-object for this tree" you could use json_agg():

SELECT json_agg(sub) AS array_of_edges
FROM  (
   SELECT DISTINCT
          node, lag(node) OVER (PARTITION BY path_id ORDER BY id) AS parent
   FROM   tbl
   ORDER  BY parent NULLS FIRST, node  -- ORDER BY optional
   ) sub;

fiddle
_{Old sqlfiddle}