SQL Server如何查询嵌套列表/对象而非平面表 输出适配Web服务的存储过程

SQL Server 2016及以上版本原生支持JSON序列化能力，通过FOR JSON子句即可直接实现你需要的嵌套结构输出，无需额外自定义扩展。

核心实现代码 #

你可以通过嵌套子查询+FOR JSON的写法实现需求，示例如下：

SELECT 
    p.Name,
    -- 子查询关联出的工作列表会自动序列化为JSON数组
    (
        SELECT j.Name, j.Position 
        FROM dbo.Jobs AS j 
        WHERE j.PersonId = p.Id
        FOR JSON PATH
    ) AS Jobs
FROM dbo.Person AS p
-- 若你需要返回单条人员数据，可加对应筛选条件
WHERE p.Name = 'test'
-- WITHOUT_ARRAY_WRAPPER参数用于去掉单条结果最外层的方括号，直接返回对象
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

上述查询的输出结果和你要求的JSON结构完全一致。

封装为Web服务可用的存储过程 #

直接将上述逻辑封装为存储过程，输出结果就是标准JSON字符串，Web服务可以直接读取使用，无需额外格式转换：

CREATE PROCEDURE dbo.GetPersonWithJobs
    @PersonId INT -- 可根据业务需求自定义输入参数，比如按人员ID筛选
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        p.Name,
        (
            SELECT j.Name, j.Position 
            FROM dbo.Jobs AS j 
            WHERE j.PersonId = p.Id
            FOR JSON PATH
        ) AS Jobs
    FROM dbo.Person AS p
    WHERE p.Id = @PersonId
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
END

补充说明 #

• 如果你需要返回多条人员的嵌套数据数组，去掉WITHOUT_ARRAY_WRAPPER参数即可
• 如果你使用的是SQL Server 2016之前的无原生JSON支持的版本，可以通过FOR XML PATH手动拼接JSON字符串，但兼容性和性能都远不如原生FOR JSON方案，优先建议升级到支持原生JSON的版本使用

内容的提问来源于stack exchange，提问作者mohamad mazaheri