代码之家 › 专栏 › 技术社区 › FDavidov

SQL Server 2016-从单个表将逻辑层次结构构建为JSON结构

sql-server-2016 sql-server json sql

FDavidov · 技术社区 · 6 年前

我有一个表,其中包含与非常大的文档相关的信息。这张桌子看起来如下:

ID      | Title         | Parent_ID     | <other columns>
--------+---------------+---------------+-------------------
0       | Root          | null          | ...
1       | Introduction  | 0             | ...
2       | Glossary      | 1             | ...
3       | Audience      | 1             | ...
4       | "A"           | 2             | ...
5       | "B"           | 2             | ...
6       | "C"           | 2             | ...

结果json应该是 <other columns> 为了清楚起见,省略部分:

{"ID"        : 0        ,
 "Title"     : "Root"   ,
 "Contents"  : [{"ID"        : 1             ,
                 "Title"     : "Introduction",
                 "Contents"  : [{"ID"          : 2           ,
                                 "Title"       : "Glossary"  ,
                                 "Contents"    : [{"ID"       : 4       ,
                                                   "Title"    : "A"     ,
                                                   "Contents" : []       },
                                                  {"ID"       : 5       ,
                                                   "Title"    : "B"     ,
                                                   "Contents" : []       },
                                                  {"ID"       : 6       ,
                                                   "Title"    : "C"     ,
                                                   "Contents" : []       }]
                                },
                                {"ID"       : 3          ,
                                 "Title"    : "Audience" ,
                                 "Contents" : []
                                }
                               ]
                },
                ....
               ]
}

我有一个简单的(递归的)过程来处理这个问题,但是希望使用DBMS的JSON能力(也许使用CTE?)有一种更简单的方法。.

1 回复 | 直到 6 年前

LukStorms 6 年前

是否知道父/子关系的最大深度?
然后你可以像在这个例子中那样:

测试 DB<gt;小提琴 here

测试数据:

CREATE TABLE documentdetails 
(
   ID INT PRIMARY KEY NOT NULL,
   Title VARCHAR(30) NOT NULL,
   Parent_ID INT,
   FOREIGN KEY (Parent_ID) REFERENCES documentdetails (ID) 
);

INSERT INTO documentdetails (ID, Title, Parent_ID) VALUES 
(1, 'Root', null), (2, 'Introduction', 1), (3, 'Glossary', 1),
(4, 'Audience', 1), (5, 'A', 2), (6,'B', 2), (7, 'C', 2), 
(8, 'Foo', null), (9, 'Bar Intro', 8), (10, 'Glossy stuff', 8), (11, 'What The Fook', 8),
(12, 'Yo', 9), (13, 'Ai', 10), (14, 'Potato', 11);

查询:

SELECT 
root.ID, 
root.Title,
(  
   SELECT lvl0.ID, lvl0.Title, 0 as Depth,
   (  
      SELECT lvl1.ID, lvl1.Title, 1 as Depth,
      ( 
         SELECT lvl2.ID, lvl2.Title, 2 as Depth,
         ( 
            SELECT lvl3.ID, lvl3.Title, 3 as Depth
            FROM documentdetails lvl3
            WHERE lvl3.Parent_ID = lvl2.ID
            FOR JSON PATH
         ) AS Contents
         FROM documentdetails lvl2
         WHERE lvl2.Parent_ID = lvl1.ID
         FOR JSON PATH
      ) AS Contents
      FROM documentdetails lvl1
      WHERE lvl1.Parent_ID = lvl0.ID
      FOR JSON PATH
   ) AS Contents
   FROM documentdetails lvl0
   WHERE lvl0.ID = root.ID
   FOR JSON PATH
) AS Contents
FROM documentdetails root
WHERE root.Parent_ID IS NULL;

结果:

ID  Title   Contents
--  -----   --------
1   Root    [{"ID":1,"Title":"Root","Depth":0,"Contents":[{"ID":2,"Title":"Introduction","Depth":1,"Contents":[{"ID":5,"Title":"A","Depth":2},{"ID":6,"Title":"B","Depth":2},{"ID":7,"Title":"C","Depth":2}]},{"ID":3,"Title":"Glossary","Depth":1},{"ID":4,"Title":"Audience","Depth":1}]}]
8   Foo     [{"ID":8,"Title":"Foo","Depth":0,"Contents":[{"ID":9,"Title":"Bar Intro","Depth":1,"Contents":[{"ID":12,"Title":"Yo","Depth":2}]},{"ID":10,"Title":"Glossy stuff","Depth":1,"Contents":[{"ID":13,"Title":"Ai","Depth":2}]},{"ID":11,"Title":"What The Fook","Depth":1,"Contents":[{"ID":14,"Title":"Potato","Depth":2}]}]}]

如果你不知道桌子的最大深度?
这里的sql使用递归cte来了解这一点。

WITH RCTE AS
(
   SELECT ID as rootID, 0 as lvl, ID, Parent_ID
   FROM documentdetails
   WHERE Parent_ID IS NULL

   UNION ALL

   SELECT r.rootID, lvl + 1, t.ID, t.Parent_ID
   FROM RCTE r
   JOIN documentdetails t ON t.Parent_ID = r.ID
)
SELECT rootID, MAX(lvl) as Depth, COUNT(*) as Nodes
FROM RCTE
GROUP BY rootID
ORDER BY MAX(lvl) DESC, COUNT(*) DESC;

或者相反,从孩子身上播种。
(如果id是主键,那么这可能会更快,因为id上有join)

WITH RCTE AS
(
   SELECT ID as baseID, 0 as lvl, ID, Parent_ID
   FROM documentdetails
   WHERE Parent_ID IS NOT NULL

   UNION ALL

   SELECT r.baseID, lvl + 1, t.ID, t.Parent_ID
   FROM RCTE r
   JOIN documentdetails t ON t.ID = r.Parent_ID
)
SELECT ID as rootID, MAX(lvl) as Depth
FROM RCTE 
WHERE Parent_ID IS NULL
GROUP BY ID
ORDER BY MAX(lvl) DESC, COUNT(*) DESC;