SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create table property ( propertyID int,
        parentID int,
        title nvarchar(100),
        typeid int,
        value nvarchar(1000),
        INDEX CX CLUSTERED (parentID)
);

insert into property (propertyID, parentID, title, typeid, [value]) VALUES(1,NULL,'foo',150, 'bar');
insert into property (propertyID, parentID, title, typeid, [value]) VALUES(2,NULL,'foo',150, 'bar');
insert into property (propertyID, parentID, title, typeid, [value]) VALUES(3,NULL,'foo',150, 'bar');
insert into property (propertyID, parentID, title, typeid, [value]) VALUES(4,3,'foo',150, 'bar');
insert into property (propertyID, parentID, title, typeid, [value]) VALUES(5,3,'foo',150, 'bar');
insert into property (propertyID, parentID, title, typeid, [value]) VALUES(6,3,'foo',150, 'bar');
insert into property (propertyID, parentID, title, typeid, [value]) VALUES(7,6,'foo',150, 'bar');
insert into property (propertyID, parentID, title, typeid, [value]) VALUES(8,7,'foo',150, 'bar');
insert into property (propertyID, parentID, title, typeid, [value]) VALUES(9,8,'foo',150, 'bar');
insert into property (propertyID, parentID, title, typeid, [value]) VALUES(10,9,'foo',150, 'bar');
insert into property (propertyID, parentID, title, typeid, [value]) VALUES(41,4,'foo',150, 'bar');
insert into property (propertyID, parentID, title, typeid, [value]) VALUES(42,4,'foo',150, 'bar');
insert into property (propertyID, parentID, title, typeid, [value]) VALUES(421,42,'foo',150, 'bar');
insert into property (propertyID, parentID, title, typeid, [value]) VALUES(411,41,'foo',150, 'bar');
14 rows affected

CREATE FUNCTION dbo.GetJson (@parentID int)
RETURNS nvarchar(max)
WITH INLINE = ON
AS BEGIN
    RETURN (
        SELECT
          propertyID,
          title,
          typeid,
          [value],
          children = JSON_QUERY(dbo.GetJson(propertyID))
        FROM property p
        WHERE EXISTS (SELECT parentID INTERSECT SELECT @parentID)
        FOR JSON PATH
    );
END;
SELECT dbo.GetJson(NULL);


(No column name)


[{"propertyID":1,"title":"foo","typeid":150,"value":"bar"},{"propertyID":2,"title":"foo","typeid":150,"value":"bar"},{"propertyID":3,"title":"foo","typeid":150,"value":"bar","children":[{"propertyID":4,"title":"foo","typeid":150,"value":"bar","children":[{"propertyID":41,"title":"foo","typeid":150,"value":"bar","children":[{"propertyID":411,"title":"foo","typeid":150,"value":"bar"}]},{"propertyID":42,"title":"foo","typeid":150,"value":"bar","children":[{"propertyID":421,"title":"foo","typeid":150,"value":"bar"}]}]},{"propertyID":5,"title":"foo","typeid":150,"value":"bar"},{"propertyID":6,"title":"foo","typeid":150,"value":"bar","children":[{"propertyID":7,"title":"foo","typeid":150,"value":"bar","children":[{"propertyID":8,"title":"foo","typeid":150,"value":"bar","children":[{"propertyID":9,"title":"foo","typeid":150,"value":"bar","children":[{"propertyID":10,"title":"foo","typeid":150,"value":"bar"}]}]}]}]}]}]