带关联查询的递归层级JSON T-SQL问题：添加关联后根项重复

问题背景 #

原本用于生成N层级嵌套JSON的递归函数，仅查询property表时能正常工作，适配任意深度的父子结构。但添加与someTable的左关联后，出现根项重复的问题，无法同时保留嵌套功能和获取关联表的额外数据。

原正常工作的函数：

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;

关联后出现问题的函数：

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;

问题原因 #

根项重复是因为左关联后，单个property记录对应了someTable中的多条记录，导致FOR JSON PATH生成了多条结构相同（仅关联字段不同）的根节点；即使是一对一关联，若关联字段存在NULL值，也可能触发重复问题。

解决方案 #

方案1：仅获取关联表的单条记录（一对一或取指定一条）

如果someTable与property是一对一关系，或你只需要每条property对应的某一条关联记录，用OUTER APPLY + TOP 1确保每条property仅返回一条结果：

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(p.propertyID))
        FROM property p
        OUTER APPLY (
            -- 取关联表中对应propertyID的第一条记录，可加ORDER BY指定规则
            SELECT TOP 1 someField
            FROM someTable s
            WHERE s.propertyID = p.propertyID
            -- ORDER BY s.createTime DESC -- 可选：按时间取最新记录
        ) s
        WHERE EXISTS (SELECT p.parentID INTERSECT SELECT @parentID)
        FOR JSON PATH
    );
END;

方案2：将关联表的多条记录合并为JSON数组（一对多场景）

如果property对应someTable的多条记录，需要把这些记录作为数组嵌套在JSON中，而不是重复根节点：

CREATE FUNCTION dbo.GetJson (@parentID int)
RETURNS nvarchar(max)
AS BEGIN
    RETURN (
        SELECT
          p.propertyID,
          p.title,
          p.typeid,
          p.[value],
          -- 将关联表的多条记录生成为JSON数组
          someFields = JSON_QUERY(
              (SELECT someField FROM someTable s WHERE s.propertyID = p.propertyID FOR JSON PATH)
          ),
          children = JSON_QUERY(dbo.GetJson(p.propertyID))
        FROM property p
        WHERE EXISTS (SELECT p.parentID INTERSECT SELECT @parentID)
        FOR JSON PATH
    );
END;

方案3：去重关联结果（适用于意外重复的场景）

如果关联后出现无意义的重复记录（比如someTable存在重复的propertyID），可以用GROUP BY去重：

CREATE FUNCTION dbo.GetJson (@parentID int)
RETURNS nvarchar(max)
AS BEGIN
    RETURN (
        SELECT
          p.propertyID,
          p.title,
          p.typeid,
          p.[value],
          -- 用聚合函数取唯一值，比如MAX/STRING_AGG
          MAX(s.someField) AS someField,
          children = JSON_QUERY(dbo.GetJson(p.propertyID))
        FROM property p
        LEFT JOIN someTable s ON s.propertyID = p.propertyID
        WHERE EXISTS (SELECT p.parentID INTERSECT SELECT @parentID)
        GROUP BY p.propertyID, p.title, p.typeid, p.[value]
        FOR JSON PATH
    );
END;

内容的提问来源于stack exchange，提问作者Chris Ward