I'm trying to recreate a JSON output that is about 5 levels deep. I can recreate 2 levels deep, but after that, I'm stumped. It has worked using FOR JSON AUTO, but I need control over the naming and how they appear, so I need to be able to use FOR JSON PATH.

Below is sample code and would allow for a 3 level deep output. I'm hoping that whatever is needed to make it 3 levels, I can learn from to go to 5 levels.

CREATE TABLE [FamilyTree]( 
   [ID] INT NOT NULL ,
   [Name] VARCHAR(250) NOT NULL,
   [ParentID] INT NOT NULL,
) ON [PRIMARY] 
GO

INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(1,N'Person1',0)
INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(2,N'Person2',0)
INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(3,N'Person3',1)
INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(4,N'Person4',2)
INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(5,N'Person5',3)
INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(6,N'Person6',3)
INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(7,N'Person7',4)
INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(8,N'Person8',4)
INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(9,N'Person9',4)

Using this query:

SELECT
FT1.Name AS [name],
(SELECT Name FROM FamilyTree WHERE ParentID = FT1.ID FOR JSON PATH) children
FROM FamilyTree FT1
WHERE FT1.ParentID = 0
FOR JSON PATH

I can get the following output:

[
    {
        "name": "Person1",
        "children": [
            {
                "Name": "Person3"
            }
        ]
    },
    {
        "name": "Person2",
        "children": [
            {
                "Name": "Person4"
            }
        ]
    }
]

but I want to be able to get it to go even further to look like this:

[
    {
        "name": "Person1",
        "children": [
            {
                "Name": "Person3",
                "children": [
                    {
                        "name": "Person 5",
                        "name": "Person 6",
                    }
                ]
            }
        ]
    },
    {
        "name": "Person2",
        "children": [
            {
                "name": "Person4",
                "children": [
                    {
                        "name": "Person 7",
                        "name": "Person 8",
                        "name": "Person 9"
                    }
                ]
            }
        ]
    }
]

CodePudding user response：

Try this, Mabe its usful:

DECLARE @FamilyTree TABLE ( 
   [ID] INT NOT NULL ,
   [Name] VARCHAR(250) NOT NULL,
   [ParentID] INT NOT NULL
) 

INSERT @FamilyTree([ID],[Name],[ParentID])
VALUES(1,N'Person1',0)
INSERT @FamilyTree([ID],[Name],[ParentID])
VALUES(2,N'Person2',0)
INSERT @FamilyTree([ID],[Name],[ParentID])
VALUES(3,N'Person3',1)
INSERT @FamilyTree([ID],[Name],[ParentID])
VALUES(4,N'Person4',2)
INSERT @FamilyTree([ID],[Name],[ParentID])
VALUES(5,N'Person5',3)
INSERT @FamilyTree([ID],[Name],[ParentID])
VALUES(6,N'Person6',3)
INSERT @FamilyTree([ID],[Name],[ParentID])
VALUES(7,N'Person7',4)
INSERT @FamilyTree([ID],[Name],[ParentID])
VALUES(8,N'Person8',4)
INSERT @FamilyTree([ID],[Name],[ParentID])
VALUES(9,N'Person9',4)

SELECT
FT1.Name AS [name],children.Name,grandchild.Name AS grandchild
FROM @FamilyTree FT1
INNER JOIN (SELECT * FROM @FamilyTree ) children ON children.ParentID = FT1.ID
INNER JOIN (SELECT * FROM @FamilyTree ) grandchild ON grandchild.ParentID = children.ID
WHERE FT1.ParentID = 0
FOR JSON AUTO

For each level, you can set another "INNER JOIN"

It's the result:

[
  {
    "name": "Person1",
    "children": [
      {
        "Name": "Person3",
        "grandchild": [
          {
            "grandchild": "Person5"
          },
          {
            "grandchild": "Person6"
          }
        ]
      }
    ]
  },
  {
    "name": "Person2",
    "children": [
      {
        "Name": "Person4",
        "grandchild": [
          {
            "grandchild": "Person7"
          },
          {
            "grandchild": "Person8"
          },
          {
            "grandchild": "Person9"
          }
        ]
      }
    ]
  }
]

CodePudding user response：

You can just keep nesting subqueries if you want

SELECT
  ft1.ID,
  ft1.Name,
  ft2.children
FROM FamilyTree ft1
CROSS APPLY (
    SELECT
      ft2.ID,
      ft2.Name,
      ft3.children
    FROM FamilyTree ft2
    CROSS APPLY (
        SELECT
          ft3.ID,
          ft3.Name,
          ft4.children
        FROM FamilyTree ft3
        CROSS APPLY (
            SELECT
              ft4.ID,
              ft4.Name,
              ft5.children
            FROM FamilyTree ft4
            CROSS APPLY (
                SELECT
                  ft5.ID,
                  ft5.Name
                FROM FamilyTree ft5
                WHERE ft5.ParentID = ft4.ID
                FOR JSON PATH
            ) ft5(children)
            WHERE ft4.ParentID = ft3.ID
            FOR JSON PATH
        ) ft4(children)
        WHERE ft3.ParentID = ft2.ID
        FOR JSON PATH
    ) ft3(children)
    WHERE ft2.ParentID = ft1.ID
    FOR JSON PATH
) ft2(children)
WHERE ft1.ParentID IS NULL
FOR JSON PATH;

db<>fiddle

To do this recursively, or for that matter to prevent duplication of code, you cannot use a Table Valued Function. You can only do this with a scalar UDF (oh the horror!).

CREATE OR ALTER FUNCTION dbo.GetJson (@ParentID int)
RETURNS nvarchar(max)
AS
BEGIN
    RETURN (
        SELECT
          ft.ID,
          ft.Name,
          children = dbo.GetJson(ft.ID)
        FROM FamilyTree ft
        WHERE EXISTS (SELECT ft.ParentID INTERSECT SELECT @ParentID)  -- null compare
        FOR JSON PATH
    );
END;

SELECT dbo.GetJson(NULL);

db<>fiddle

Note that in both of these examples, root nodes have a ParentID of NULL not 0. This is the correct way to do it, as there is no 0 row.