Get Hieararchical data as JSON from SQL Server

Question

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;

Answer 1

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

You can self-join all nodes and check if they are a child using GetAncestor

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.GetAncestor(1) = @parentID
        FOR JSON PATH
    );
END;

go

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

db<>fiddle

Result

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