I found this link and it works as long as I don't join to other tables Generate nested nth level JSON SQL Server using recursive CTE
The problem is some of the data I need is via a join. The below is from the original link and it works.
The data for parent child is nth levels deep so the original function works as I expected.
CREATE FUNCTION dbo.GetJson (@parentID int)
RETURNS nvarchar(max)
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;
If I start adding joins
CREATE FUNCTION dbo.GetJson (@parentID int)
RETURNS nvarchar(max)
AS BEGIN
RETURN (
SELECT
p.propertyID,
p.title,
p.typeid,
p.[value],
s.someField,
children = JSON_QUERY(dbo.GetJson(propertyID))
FROM property p
LEFT OUTER JOIN someTable s ON s.propertyID = p.propertyID
WHERE EXISTS (SELECT parentID INTERSECT SELECT @parentID)
FOR JSON PATH
);
END;
When I add any joins the root item seems to be repeated and I've not been able to figure out how to keep this working correctly in its original state and add in the joins I need to grab some extra data.