SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Tree (
  "Id" INTEGER,
  "HierarchyId" hierarchyid,
  "Name" VARCHAR(20)
);

INSERT INTO Tree
  ("Id", "HierarchyId", "Name")
VALUES
  ('1', '/1/', 'MHz'),
  ('2', '/1/2/', 'Finance'),
  ('3', '/1/3/', 'IT'),
  ('4', '/1/3/4/', 'Software Development'),
  ('5', '/1/3/5/', 'QA'),
  ('6', '/1/2/6/', 'Accountant');
6 rows affected

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;
SELECT dbo.GetJson(hierarchyid::GetRoot());


(No column name)


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