sql-server - 如何使用 Microsoft SQL Server 2016 生成分层 JSON 数据？

Question

我使用的是Microsoft SQL Server 2016。该版本支持JSON。

我有一个包含以下数据的Person表:

<表类=“s-表”><标题>人员 ID父亲ID姓名 <正文>1空第四祖父21第三祖父32第二祖父43祖父54父亲64叔叔76表弟85兄弟95我

我运行以下查询:

WITH Persons_CTE AS(
    SELECT PersonId, FatherId, Name FROM Persons WHERE FatherId IS NULL
    UNION ALL
    SELECT P.PersonId, P.FatherId, P.Name FROM Persons P JOIN Persons_CTE PCTE
    ON PCTE.PersonId = P.FatherId)

    SELECT P.Name as Name, PCTE.Name as Children FROM Persons_CTE PCTE LEFT JOIN Persons P
    ON P.PersonId = PCTE.FatherId 
    FOR JSON PATH

查询生成以下结果:

[
   {
      "Children":"4th Grand Father"
   },
   {
      "Name":"4th Grand Father",
      "Children":"3rd Grand Father"
   },
   {
      "Name":"3rd Grand Father",
      "Children":"2nd Grand Father"
   },
   {
      "Name":"2nd Grand Father",
      "Children":"Grand Father"
   },
   {
      "Name":"Grand Father",
      "Children":"Father"
   },
   {
      "Name":"Grand Father",
      "Children":"Uncle"
   },
   {
      "Name":"Uncle",
      "Children":"Cousin"
   },
   {
      "Name":"Father",
      "Children":"Brother"
   },
   {
      "Name":"Father",
      "Children":"Me"
   }
]

我希望查询结果为以下分层格式。我该怎么做？

[
   {
      "Name":"4th Grand Father",
      "Children":[
         {
            "Name":"3rd Grand Father",
            "Children":[
               {
                  "Name":"2nd Grand Father",
                  "Children":[
                     {
                        "Name":"Grand Father",
                        "Children":[
                           {
                              "Name":"Father",
                              "children":[
                                 {
                                    "Name":"Brother"
                                 },
                                 {
                                    "Name":"Me"
                                 }
                              ]
                           },
                           {
                              "Name":"Uncle",
                              "children":[
                                 {
                                    "Name":"Cousin"
                                 }
                              ]
                           }
                        ]
                     }
                  ]
               }
            ]
         }
      ]
   }
]

Answer 1

当然，使用递归 CTE 实现 Json 树会非常困难(如果不是不可能的话)。

但是，使用递归函数确实很有帮助并解决了问题:

ALTER  FUNCTION fn_Json(@PersonId INT, @IsRoot INT ) 
RETURNS VARCHAR(MAX)
BEGIN 
    DECLARE @Json NVARCHAR(MAX) = '{}', @Name NVARCHAR(MAX) , @Children                 NVARCHAR(MAX)

    SET @Json =  
    (SELECT P.Name ,JSON_QUERY(dbo.fn_Json(P.PersonId, 2) ) AS Children 
    FROM dbo.Persons AS P  
    WHERE P.FatherId = @PersonId 
    FOR JSON AUTO);

    IF(@IsRoot = 1) 
    BEGIN
       SELECT @Name = P.Name FROM dbo.Persons AS P WHERE P.PersonId = @PersonId
       SET @Json =   '{"Name":"' + @Name + '","Children":' + CAST(@Json AS NVARCHAR(MAX)) + '}'
       SET @IsRoot = 2
    END

    RETURN @Json 
END 

开始

值得一提的是，如果函数的内部对象无效，则无法构建函数。因此，需要将函数构建为:

CREATE FUNCTION fn_Json(@PersonId INT, @IsRoot INT) 
RETURNS VARCHAR(MAX)
BEGIN 
   RETURN 1
END 

然后使用第一个代码。如果您希望包含根节点，请设置

@IsRoot = 1

如果不是@IsRoot = 2或其他一些值