0

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.

1 Answer 1

0

Turns out it was bad data in the database I was using. The table being joined to had some duplicate rows that shouldn't of been there (dev database go figure) once I noticed that I was able to change the join to return only 1 row and that fixed the issue.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.