Get Hieararchical data as JSON from SQL Server-CodePudding

I have following table:

Id	HieararchyId	Name
1	/	MHz
2	/1/	Finance
3	/1/	IT
4	/1/3/	Software Development
5	/1/3/	QA
6	/1/2/	Accountant

and I want to get data for my TreeView like:

[
{
  "Id": 1,
  "HierarchyId": "/"
  "Name":"MHz",
  "Children":[
     {
        "Id": 3,
        "HierarchyId": "/1/"
        "Name":"IT",
        "Children":[
           {
              "Id": 4,
              "HierarchyId": "/1/3/"
              "Name":"Software Development",
              "Children":[]
           }
           {
              "Id": 5,
              "HierarchyId": "/1/3/"
              "Name":"QA",
              "Children":[]
           }
        ]
     },
     {
        "Id": 2,
        "HierarchyId": "/1/"
        "Name":"Finance",
        "Children":[
           {
              "Id": 6,
              "HierarchyId": "/1/2/"
              "Name":"Accountant",
              "Children":[]
           }
        ]
     }
   ]
 }
]

I tried with this: How to generate hierarchical JSON data

Got error: No more lock classes available from transaction.

Or is it better creating recursive function and filling the Tree on client side?

Update

CREATE FUNCTION dbo.GetJson (@departmentId hierarchyid)
RETURNS nvarchar(max)
AS BEGIN
RETURN (
    SELECT
      Id,
      HierarchyId,
      Name,
      children = JSON_QUERY(dbo.GetJson(HierarchyId))
    FROM Departments p
    WHERE p.HierarchyId.IsDescendantOf(@departmentId ) = 1
    FOR JSON PATH
);
END;

CodePudding user response：

The best way I have found to do this is to use a recursive function.

You can get all descendants of a node using IsDescendantOf but you need to make sure to exclude the current node

CREATE FUNCTION dbo.GetJson (@parentID hierarchyid)
RETURNS nvarchar(max)
AS BEGIN
    RETURN (
        SELECT
          t.Id,
          t.HierarchyId,
          t.Name,
          children = JSON_QUERY(dbo.GetJson(t.HierarchyId))
        FROM Tree t
        WHERE t.HierarchyId <> @parentID
          AND t.HierarchyId.IsDescendantOf(@parentID) = 1
        FOR JSON PATH
    );
END;

go

SELECT dbo.GetJson(hierarchyid::GetRoot());

db<>fiddle

Result

[
  {
    "Id": 2,
    "HierarchyId": "/1/",
    "Name": "Finance",
    "children": [
      {
        "Id": 4,
        "HierarchyId": "/1/3/",
        "Name": "Software Development"
      },
      {
        "Id": 5,
        "HierarchyId": "/1/3/",
        "Name": "QA"
      },
      {
        "Id": 6,
        "HierarchyId": "/1/2/",
        "Name": "Accountant"
      }
    ]
  },
  {
    "Id": 3,
    "HierarchyId": "/1/",
    "Name": "IT",
    "children": [
      {
        "Id": 4,
        "HierarchyId": "/1/3/",
        "Name": "Software Development"
      },
      {
        "Id": 5,
        "HierarchyId": "/1/3/",
        "Name": "QA"
      },
      {
        "Id": 6,
        "HierarchyId": "/1/2/",
        "Name": "Accountant"
      }
    ]
  },
  {
    "Id": 4,
    "HierarchyId": "/1/3/",
    "Name": "Software Development"
  },
  {
    "Id": 5,
    "HierarchyId": "/1/3/",
    "Name": "QA"
  },
  {
    "Id": 6,
    "HierarchyId": "/1/2/",
    "Name": "Accountant"
  }
]