SQL Server FOR JSON 如何返回带分页总行数的JSON结果

不需要放弃单查询写法，也不需要把分页结果存入变量二次拼接，你之前的问题是对FOR JSON的嵌套逻辑理解有误，用FOR JSON PATH替代FOR JSON AUTO即可直接生成符合要求的结构。

问题原因 #

FOR JSON AUTO会根据查询列的来源表/派生表自动决定嵌套层级，你把标量变量@TotalRows和分页数据列放在同一个查询层级，引擎会默认把它归到每一行数据的属性里，就出现了TotalRows出现在每个数组元素中的问题；如果强行把@TotalRows提到外层，又会因为列来源不匹配触发编译错误。

可直接运行的修正代码 #

DECLARE @TotalRows INT, @Page INT, @noRows INT
-- 存储过程中这两个参数为入参，此处仅做示例赋值
SET @Page = 0
SET @noRows = 20

-- 计算匹配条件的总行数，注意WHERE条件和下方分页查询完全一致
SELECT @TotalRows = COUNT(*) 
FROM dbo.tblRequisitions aa

SET DATEFORMAT dmy

SELECT
    @TotalRows AS TotalRows,
    JSON_QUERY( -- 用JSON_QUERY告诉引擎这是已经序列化好的JSON片段，不要做转义处理
        SELECT 
            RequisitionID  AS ID,
            RequisitionRef AS Ref,
            RequisitionJobTitle AS JobTitle,
            DATEADD(dd,14, RequisitonAdded) AS ShortlistDueDate,
            DATEDIFF(dw, GETDATE(), DATEADD(dd,14, RequisitonAdded)) AS WrkDaysToShortlistDeadline,
            RequisitonNoPositionsTotal * 5 AS NoRequiredForShortlist
        FROM dbo.tblRequisitions aa
        -- 此处添加和上方算总行数一致的WHERE筛选条件
        ORDER BY RequisitonAdded
        OFFSET (@Page * @noRows) ROWS
        FETCH NEXT @noRows ROWS ONLY
        FOR JSON PATH, INCLUDE_NULL_VALUES
    ) AS Requisitions
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

关键说明 #

• 内层分页查询单独加FOR JSON PATH，会直接序列化为当前页数据的JSON数组，外层用JSON_QUERY包裹，避免引擎把这个JSON字符串当成普通文本加转义符
• 外层查询平级放置TotalRows和序列化后的数组字段，最后用FOR JSON PATH, WITHOUT_ARRAY_WRAPPER输出单个顶层JSON对象，完全匹配你需要的结构
• 去掉了原代码里多余的一层派生表和外层排序，分页排序在内层完成即可，外层不需要额外排序
• 如果后续筛选条件复杂，不想维护两份相同的WHERE逻辑，可以改用窗口函数COUNT(*) OVER() AS TotalRows在分页查询里同时拿到总行数，不需要单独写COUNT查询，适合筛选逻辑经常变动的场景。

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