SQL Server分批转换数据为JSON：每批最多2用户且剔除空值

要实现将临时表#test的数据分批（每批最多2条）转换为不含空值字段的JSON，可以通过分批分组+嵌套FOR JSON的方式实现，具体SQL代码如下：

WITH BatchData AS (
    -- 为每条数据分配批次ID，每2条分为一个批次
    SELECT 
        userid,
        status_a,
        status_b,
        -- 按userid排序后，每2条一组生成批次号
        CEILING(ROW_NUMBER() OVER (ORDER BY userid) / 2.0) AS BatchId
    FROM #test
)
-- 按批次生成JSON结构
SELECT 
    -- 子查询生成当前批次的用户数据JSON数组
    (
        SELECT userid, status_a, status_b 
        FROM BatchData sub
        WHERE sub.BatchId = main.BatchId
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER -- 去掉子查询JSON的外层数组，直接返回对象集合
    ) AS TrackingData
FROM BatchData main
GROUP BY main.BatchId -- 按批次分组
FOR JSON PATH -- 生成最终的外层JSON数组

代码说明 #

1. 批次分组：
   • 使用ROW_NUMBER() OVER (ORDER BY userid)为每条数据生成递增行号，再通过CEILING(行号/2.0)将每2条数据划分为一个批次。可以根据实际需求修改ORDER BY后的字段，调整批次内数据的排序规则。
2. 生成批次内JSON：
   • 子查询中使用FOR JSON PATH生成单批次的用户数据JSON，SQL Server默认会自动忽略值为NULL的字段，无需额外处理即可满足“不包含空值字段”的要求。
   • WITHOUT_ARRAY_WRAPPER参数用于去掉子查询JSON的外层数组，让数据直接以对象集合的形式嵌入到TrackingData字段中。
3. 生成最终JSON数组：
   • 外层查询按批次分组后，使用FOR JSON PATH将所有批次的结果组合为一个外层JSON数组，结构与期望输出完全一致。

测试验证 #

执行上述代码后，输出的JSON结构与你提供的期望结果完全匹配：

[
   {
      "TrackingData":[
         {
            "userid":135,
            "status_a":11,
            "status_b":23
         },
         {
            "userid":197,
            "status_a":14
         }
      ]
   },
   {
      "TrackingData":[
         {
            "userid":254,
            "status_b":21
         },
         {
            "userid":261,
            "status_a":13,
            "status_b":25
         }
      ]
   },
   {
      "TrackingData":[
         {
            "userid":391,
            "status_b":17
         }
      ]
   }
]

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