PostgreSQL - 从包含路径的表中生成JSON树对象

Question

假设有一个包含未知树结构的路径/节点的表：

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

相应的树形结构如下：

    n1 
   /  
  n2
 /  \
n3   n4

是否可能使用SQL和PostgreSQL函数为此树生成JSON对象？

- tbz

---

不确定您在数据库中如何构建树形结构；考虑使用自引用的 parent_id，或修改的前序遍历 http://www.sitepoint.com/hierarchical-data-database-2/ - pozs

我不明白那个节点列表是如何转换成那个图的。它看起来不像传统的边缘列表。 - Craig Ringer

按照id排序，每个路径由多个节点和连接这些节点的边组成，例如路径p1按照特定顺序包含节点n1、n2和n3。路径的第一个节点始终是树的根节点。 - tbz

Answer 1

看起来你有一组路径，其中部分重叠。
首先删除重复的边：

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 对于根节点来说是 NULL。您可能想要将这个“非边缘”从结果中删除。
然后，要为此树生成 JSON 对象，您可以使用 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.

- Erwin Brandstetter