I wish to convert data to JSON with separate batches. In each batch there should be no more than two users, without null values.

create table #test(userid int, status_a int, status_b int)

insert into #test values  (135, 11,23),
                          (197, 14, null),
                          (254, null,21),
                          (261, 13, 25),
                          (391, null, 17)

result should be

[
   {
      "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
         }
      ]
   }
]

I tried to do this, but don't know how to divide into to batches

SELECT *
FROM #test
FOR JSON PATH

CodePudding user response：

Looks like you are ordering by userid and you want the first two in a TrackingData object, next pair in a TrackingData object and so on.

This returns your desired results

DECLARE @BatchSize INT = 2;

WITH T AS
(
SELECT *, 
       RN = ROW_NUMBER() OVER (ORDER BY userid) - 1,
       Json = (SELECT t.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER )
FROM #test t
)
SELECT TrackingData = JSON_QUERY('['   
                                  STRING_AGG(Json, ',') WITHIN GROUP (ORDER BY RN)   
                                  ']')
FROM T
GROUP BY RN/@BatchSize
ORDER BY RN/@BatchSize
FOR JSON PATH

It generates a zero based sequential row numbering and uses integer division on that to divide into <@BatchSize> sized groups.

The construction of the sub array is just done with string aggregation and wrapped in JSON_QUERY so it is treated as JSON and not escaped in the final FOR JSON PATH.