How to generate hierarchical JSON data with Microsoft SQL Server 2016?

Question

I'm using Microsoft SQL Server 2016. This version supports JSON.

I have a Person table with the following data:

PersonId	FatherId	Name
1	NULL	4th Grand Father
2	1	3rd Grand Father
3	2	2nd Grand Father
4	3	Grand Father
5	4	Father
6	4	Uncle
7	6	Cousin
8	5	Brother
9	5	Me

I run the following query:

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

The query generates the following result:

[
   {
      "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"
   }
]

I want the query result to the following hierarchical format. How can I do it?

[
   {
      "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"
                                 }
                              ]
                           }
                        ]
                     }
                  ]
               }
            ]
         }
      ]
   }
]

Afshin Amiri · Accepted Answer · 2017-01-24 12:21:06Z

15

Sure It would be very hard (if not impossible) to implement a Json tree using recursive CTE.

However, using recursive function is really helpful and solves the Problem:

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

GO

It worth mentioning that functions can not be built if its interior objects are invalid. Therefore, it is necessary to build the function as:

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

and then to use the fist code. If you want that the root node is included set

@IsRoot = 1

if not @IsRoot = 2 or some other values

edited Jan 24, 2017 at 12:21

answered Jan 24, 2017 at 8:26

Afshin Amiri

3,6231 gold badge22 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Luis Cabrera Over a year ago

Afshin Amiri: pardon my ignorance but how would you call the function in your example? I have a similar need and I'm trying to use your function but not working for me. I'm sure I'm doing something wrong

Afshin Amiri Over a year ago

Just use "select fn_Json(@PersonId INT, @IsRoot INT)" if you are using SQL Server and replace the parameters

Jovan MSFT · Accepted Answer · 2016-10-09 17:28:13Z

6

Unfortunately, recursive CTe cannot be used to generate hierarchical json . Output of recursive CTE is still flat result.

The only way to create hierarchical output is to create separate CTE for each level and then join the using FOR JSON AUTO

Prepare table:

declare @t table (PersonId int, FatherId int, Name nvarchar(20));

insert into @t(PersonId, FatherId, Name)
values
(1, NULL, '4th Grand Father'),
(2, 1, '3rd Grand Father'),
(3, 2, '2nd Grand Father'),
(4, 3, 'Grand Father'),
(5, 4, 'Father'),
(6, 4, 'Uncle'),
(7, 6, 'Cousin'),
(8, 5, 'Brother'),
(9, 5, 'Me');

-- Hierarchical query:

WITH
Persons_CTE1 AS(
    SELECT PersonId, FatherId, Name FROM @t WHERE FatherId IS NULL
),
Persons_CTE2 AS(
    SELECT P.PersonId, P.FatherId, P.Name
    from @t P
    WHERE P.FatherId IN (SELECT PersonId FROM Persons_CTE1)
),
Persons_CTE3 AS(
    SELECT P.PersonId, P.FatherId, P.Name
    from @t P
    WHERE P.FatherId IN (SELECT PersonId FROM Persons_CTE2)
),
Persons_CTE4 AS(
    SELECT P.PersonId, P.FatherId, P.Name
    from @t P
    WHERE P.FatherId IN (SELECT PersonId FROM Persons_CTE3)
),
Persons_CTE5 AS(
    SELECT P.PersonId, P.FatherId, P.Name
    from @t P
    WHERE P.FatherId IN (SELECT PersonId FROM Persons_CTE4)
),
Persons_CTE6 AS(
    SELECT P.PersonId, P.FatherId, P.Name
    from @t P
    WHERE P.FatherId IN (SELECT PersonId FROM Persons_CTE5)
)
select Persons_CTE1.Name, Persons_CTE2.Name, Persons_CTE3.Name,
       Persons_CTE4.Name, Persons_CTE5.Name, Persons_CTE6.Name
from Persons_CTE1
    LEFT JOIN Persons_CTE2
        ON Persons_CTE2.FatherId = Persons_CTE1.PersonId
    LEFT JOIN Persons_CTE3
        ON Persons_CTE3.FatherId = Persons_CTE2.PersonId
    LEFT JOIN Persons_CTE4
        ON Persons_CTE4.FatherId = Persons_CTE3.PersonId
    LEFT JOIN Persons_CTE5
        ON Persons_CTE5.FatherId = Persons_CTE4.PersonId
    LEFT JOIN Persons_CTE6
        ON Persons_CTE6.FatherId = Persons_CTE5.PersonId
FOR JSON AUTO

answered Oct 9, 2016 at 17:28

Jovan MSFT

14.8k4 gold badges44 silver badges59 bronze badges

3 Comments

sksallaj Over a year ago

There's no way to dynamically do a CTE for each level? You'd have to expect 6 levels in your query right? Like what if there's 7 levels?

user260778 Over a year ago

This method is working. But what if the table is very large and there are many levels like say 17 levels. A dynamic query can be constructed and run with sp_executesql but what to do about the huge number of table rows ?

ChandraSaiKarthik Over a year ago

Dynamic CTE is possible with Dynamic SQL and @MAXlevel is calculated. Refer the Answer https://stackoverflow.com/a/76142795/6852451. You can analyse the output JSON data also visually for more clarity and easy understanding.

ChandraSaiKarthik · Accepted Answer · 2023-04-30 18:05:38Z

Table Schema and Sample Data

DROP TABLE IF EXISTS dbo.Person;
CREATE TABLE dbo.Person (PersonId INT PRIMARY KEY, FatherId INT, Name VARCHAR(50));
--
INSERT INTO dbo.Person(PersonId, FatherId, Name)
VALUES
    (1, NULL, '4th Grand Father'),
    (2, 1, '3rd Grand Father'),
    (3, 2, '2nd Grand Father'),
    (4, 3, 'Grand Father'),
    (5, 4, 'Father'),
    (6, 4, 'Uncle'),
    (7, 6, 'Cousin'),
    (8, 5, 'Brother'),
    (9, 5, 'Me');

Get the Maximum Depth for the Hierarchial Data.

DECLARE @MAXlvl INT;

;WITH _CTE AS 
(
    SELECT C.PersonId, C.FatherId, CAST(0 AS INT) AS "level" FROM dbo.Person AS C WHERE C.FatherId IS NULL
    UNION ALL
    SELECT C.PersonId, C.FatherId, P.level+1 AS "level" FROM dbo.Person AS C JOIN _CTE AS P ON C.FatherId=P.PersonId
)
SELECT @MAXlvl = MAX(level)+1 FROM _CTE;

Now with this @MAXlvl, Using DynamicSQL and We Travel from Bottom to Top of Hierarchy to Generate JSONdata at Each Level.

DECLARE @SQLstring NVARCHAR(MAX);

WHILE @MAXlvl > 0
BEGIN
    SET @SQLstring =
        'SELECT P'+CAST(@MAXlvl AS VARCHAR(10))+'.PersonId AS "PersonId", P'+CAST(@MAXlvl AS VARCHAR(10))+'.Name AS "Name"'
        +       ISNULL(',('+@SQLstring+') AS "Children"', '')
        +' FROM dbo.Person AS P'+CAST(@MAXlvl AS VARCHAR(1))
        +' WHERE P'+CAST(@MAXlvl AS VARCHAR(10))+'.FatherId '+CASE WHEN @MAXlvl-1>0 THEN '= P'+CAST(@MAXlvl-1 AS VARCHAR(10))+'.PersonId' ELSE 'IS NULL' END
        +' FOR JSON PATH';
    --
    SET @MAXlvl = @MAXlvl - 1;
END;
--PRINT (@SQLstring);
EXEC sys.sp_executesql @command = @SQLstring;
GO

You can analyse the JSONdata Generated using https://jsoncrack.com/editor

JSON output as Hierarchial Data Representation

@sksallaj, I think this is what you are referring to unknown levels of Hierarchy. You can try this solution and it should work for any hierarchy levels as it calculates the hierarchy levels dynamically.

Collectives™ on Stack Overflow

How to generate hierarchical JSON data with Microsoft SQL Server 2016?

3 Answers 3

However, using recursive function is really helpful and solves the Problem:

2 Comments

3 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

However, using recursive function is really helpful and solves the Problem:

2 Comments

3 Comments

1 Comment

Linked

Related