我有一个嵌套集：
id;parent;name;lft;rgt
----------------------
1;0;"Food";2;21
3;1;"Appetizer";3;8
8;3;"Nachos & salsa";4;5
9;3;"Kentucky chicken wings";6;7
4;1;"Soup";9;14
10;4;"Broth";10;11
11;4;"Tomato soup";12;13
5;1;"Pizza";15;20
12;5;"Americana";16;17
13;5;"Margherita";18;19
2;0;"Beverages";22;27
6;2;"Wines";23;24
7;2;"Soft drinks";25;26
首先要做的是以这样的方式查询行，以便可以检索到树。为此，我们可以简单地使用递归查询。假设您的表名为food，以下查询是一个很好的递归查询示例:
WITH RECURSIVE t AS (
SELECT f.id, f.name, f.parent, f.lft, f.rgt, array[f.name] AS path, 0 AS level
FROM food f
WHERE f.parent = 0
UNION ALL
SELECT f.id, f.name, f.parent, f.lft, f.rgt, t.path || f.name, level+1
FROM food f JOIN t ON f.parent = t.id
)
SELECT repeat('|__', level)||t.name AS tree, level, path
FROM t
ORDER BY path;
将以以下形式返回：
tree | level | path
------------------------------+-------+-------------------------------------------
Beverages | 0 | {Beverages}
|__Soft drinks | 1 | {Beverages,"Soft drinks"}
|__Wines | 1 | {Beverages,Wines}
Food | 0 | {Food}
|__Appetizer | 1 | {Food,Appetizer}
|__|__Kentucky chicken wings | 2 | {Food,Appetizer,"Kentucky chicken wings"}
|__|__Nachos & salsa | 2 | {Food,Appetizer,"Nachos & salsa"}
|__Pizza | 1 | {Food,Pizza}
|__|__Americana | 2 | {Food,Pizza,Americana}
|__|__Margherita | 2 | {Food,Pizza,Margherita}
|__Soup | 1 | {Food,Soup}
|__|__Broth | 2 | {Food,Soup,Broth}
|__|__Tomato soup | 2 | {Food,Soup,"Tomato soup"}
(13 rows)
path 数组将为我们提供一个元素来按照树形结构对行进行排序（如果你愿意，也可以使用名字以外的其他列），而 level（基本上是路径长度-1）则给出元素所在的级别。有了这两个信息，我们可以使用一些技巧与窗口函数lead（与窗口 ORDER BY path一起使用）来查看下一行的 level 并在每行创建我们的JSON（请检查查询中的注释）：WITH RECURSIVE t AS (
SELECT f.id, f.name, f.parent, f.lft, f.rgt, array[f.name] AS path, 0 AS level
FROM food f
WHERE f.parent = 0
UNION ALL
SELECT f.id, f.name, f.parent, f.lft, f.rgt, t.path || f.name, level+1
FROM food f JOIN t ON f.parent = t.id
)
SELECT (E'[\n'||string_agg(json, E'\n')||E'\n]')::json FROM (
SELECT
/* Add some simple indentation (why not?) */
repeat(' ', level)
|| '{"name":'||to_json(name)|| ', "items":['
||
/* The expr bellow will return the level of next row, or -1 if it is last */
CASE coalesce(lead(level) OVER(ORDER BY path), -1)
/* Next row opens a new level, so let's add the items array */
WHEN level+1 THEN ''
/* WHEN level+1 THEN ', "items":[' */
/* We are on the same level, so just close the current element */
WHEN level THEN ']},'
/* Last row, close the current element and all other levels still opened (no indentation here, sorry) */
WHEN -1 THEN ']}' || repeat(']}', level)
/* ELSE, the next one belongs to another parent, just close me and my parent */
ELSE /* closes me: */ ']}' /* closes my parent: */ || E'\n'||repeat(' ', level-1)||']},'
END AS json
FROM t
) s1;
这将给我们以下 JSON：
[
{"name":"Beverages", "items":[
{"name":"Soft drinks", "items":[]},
{"name":"Wines", "items":[]}
]},
{"name":"Food", "items":[
{"name":"Appetizer", "items":[
{"name":"Kentucky chicken wings", "items":[]},
{"name":"Nachos & salsa", "items":[]}
]},
{"name":"Pizza", "items":[
{"name":"Americana", "items":[]},
{"name":"Margherita", "items":[]}
]},
{"name":"Soup", "items":[
{"name":"Broth", "items":[]},
{"name":"Tomato soup", "items":[]}]}]}
]
这是一个有点棘手的问题，希望评论能够提供帮助（也希望它对所有测试用例都正确）。
1,0,'Food',2,21
2,0,'Beverages',22,27
3,1,'Appetizer',3,8
4,3,'Soup',9,14 -- Parent soup is 3 instead 1
5,1,'Pizza',15,20
6,2,'Wines',23,24
7,2,'Soft drinks',25,26
8,3,'Nachos & salsa',4,5
9,3,'Kentucky chicken wings',6,7
10,4,'Broth',10,11
11,4,'Tomato soup',12,13
12,5,'Americana',16,17
13,5,'Margherita',18,19
tree | level | path
------------------------------+-------+-------------------------------------------
Beverages | 0 | {Beverages}
|__Soft drinks | 1 | {Beverages,"Soft drinks"}
|__Wines | 1 | {Beverages,Wines}
Food | 0 | {Food}
|__Appetizer | 1 | {Food,Appetizer}
|__|__Kentucky chicken wings | 2 | {Food,Appetizer,"Kentucky chicken wings"}
|__|__Nachos & salsa | 2 | {Food,Appetizer,"Nachos & salsa"}
|__|__Soup | 2 | {Food,Appetizer,Soup}
|__|__|__Broth | 2 | {Food,Appetizer,Soup,Broth}
|__|__|__Tomato soup | 3 | {Food,Appetizer,Soup,"Tomato soup"}
|__Pizza | 1 | {Food,Pizza}
|__|__Americana | 2 | {Food,Pizza,Americana}
|__|__Margherita | 2 | {Food,Pizza,Margherita}
(13 rows)
。
WITH RECURSIVE t AS (
SELECT f.id, f.name, f.parent, f.lft, f.rgt, array[f.name] AS path, 0 AS level
FROM food f
WHERE f.parent = 0
UNION ALL
SELECT f.id, f.name, f.parent, f.lft, f.rgt, t.path || f.name, level+1
FROM food f JOIN t ON f.parent = t.id
)
SELECT ( '[' || string_agg( json, '' ) || ']' ) :: json FROM (
select
'{"name":'||to_json( name ) ||
case lead( level, 1 ) OVER( ORDER BY path )
when level then '},' --same lavel, no children, only close
when level + 1 THEN ', "items":[' -- There's children, add item array
else -- last child in group start to close
'}' || --close actual element
case
when lead( level ) OVER( ORDER BY path ) < level THEN -- last children in group, close parents, until next level
repeat( ']}', level - lead( level ) OVER( ORDER BY path ) ) || ','
else repeat( ']}', level ) -- last element in list, close parents all levels
end
end as json
from t
) s1;
缩进？？？或许以后会有...
编辑 添加代码演示 http://sqlfiddle.com/#!15/187e5/1