我有此嵌套集:
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
我想要一个代表完整树的JSON输出。
我想从JSON构建这样的嵌套列表:
http://demos.jquerymobile.com/1.2.1/docs/lists/lists-nested.html#时间
感谢您的任何帮助!
要做的第一件事是查询行以可以检索树的方式。为此,我们可以简单地使用递归查询。假设您的表被命名为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