SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tTree(
     id INTEGER IDENTITY(1,1)
    ,text VARCHAR(256)
    ,parentId INTEGER
    ,path VARCHAR(256)
    ,depth TINYINT
    ,leaf TINYINT
    ,expanded TINYINT
);

INSERT INTO  tTree(
     text
    ,parentId
    ,path
    ,depth
    ,leaf
    ,expanded
)
VALUES(
    'Category 1',null,'1',1,null,1),(
    'SubCategory 1',1,'1,2',2,null,1),(
    'Element 1',2,'1,2,3',3,1,null),(
    'Category 2',null,'4',1,null,1),(
    'SubCategory 2',4,'4,5',2,1,null),(
    'SubCategory 3',4,'4,6',2,1,null),(
    'Element 2',4,'4,7',2,null,1),(
    'SubElement 1',5,'4,5,8',3,1,null),(
    'SubSubCategory 1',2,'1,2,9',3,1,null),(
    'Category 3',null,'10',1,1,null);
    
SELECT * FROM #tTree

    
Msg 208 Level 16 State 0 Line 31
Invalid object name '#tTree'.

CREATE FUNCTION dbo.GetJson(@parentId int, @path nvarchar(1000), @depth int)
RETURNS nvarchar(max)
AS BEGIN

RETURN (
    SELECT
      t.id,
      t.text,
      t.parentId,
      path = CONCAT(@path + ',', t.id),
      depth = @depth + 1,
      t.leaf,
      t.expanded,
      children = JSON_QUERY(dbo.GetJson(t.id, CONCAT(@path + ',', t.id), @depth + 1))
    FROM tTree t
    WHERE EXISTS (SELECT t.parentId INTERSECT SELECT @parentId)  -- nullable compare
    FOR JSON PATH
);

END;
SELECT dbo.GetJson(NULL, NULL, 0);


(No column name)


[{"id":1,"text":"Category 1","path":"1","depth":1,"expanded":1,"children":[{"id":2,"text":"SubCategory 1","parentId":1,"path":"1,2","depth":2,"expanded":1,"children":[{"id":3,"text":"Element 1","parentId":2,"path":"1,2,3","depth":3,"leaf":1},{"id":9,"text":"SubSubCategory 1","parentId":2,"path":"1,2,9","depth":3,"leaf":1}]}]},{"id":4,"text":"Category 2","path":"4","depth":1,"expanded":1,"children":[{"id":5,"text":"SubCategory 2","parentId":4,"path":"4,5","depth":2,"leaf":1,"children":[{"id":8,"text":"SubElement 1","parentId":5,"path":"4,5,8","depth":3,"leaf":1}]},{"id":6,"text":"SubCategory 3","parentId":4,"path":"4,6","depth":2,"leaf":1},{"id":7,"text":"Element 2","parentId":4,"path":"4,7","depth":2,"expanded":1}]},{"id":10,"text":"Category 3","path":"10","depth":1,"leaf":1}]