PostgreSQL - generate a JSON tree object from a table containing paths

Question

For a table containing unknown paths / nodes of an unknown tree structure:

| id | path_id | node
| 1  | p1      | n1
| 2  | p1      | n2
| 3  | p1      | n3
| 4  | p2      | n1
| 5  | p2      | n2
| 6  | p2      | n4

The corresponding tree structure will be

    n1 
   /  
  n2
 /  \
n3   n4

Is it possible to create a JSON object for this tree using SQL and PostgreSQL functions?

Answer 1

It looks like you have a list of paths where the parts overlap.
First remove the duplicate edges:

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

parentis NULL for the root of the node. You can remove this "non-edge" from the result.
Then,, generate a JSON-object for this treeyou can 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;

SQL Fiddle