0

I have Query for result JSON but I need result like this:

result:[ "B":{1628424359816,35},
         "A":{1628424359816,1},
         "B":{1628424380816,63},
         "A":{1628424380816,1}]

My query :

          ;WITH CTE AS (         
           SELECT DATETIME,[B],[A], 
ROW_NUMBER() OVER (ORDER BY datetime) - ROW_NUMBER() OVER (PARTITION BY dateadd(minute,1+(datediff(minute, 0, t.DATETIME)/1 )*1, 0) ORDER BY t.DATETIME) AS RN
        FROM test t        WHERE DATETIME >='2021-05-29' AND DATETIME <='2021-08-30'       ), 
        ctmax as (        select          MIN(DATETIME) AS [FIRST],        Max(DATETIME) as [last],        min(rn) as rown
                  FROM cte          group by RN                )
    SELECT DISTINCT DATEDIFF_BIG(MILLISECOND,'1970-01-01 00:00:00',CTE.DATETIME)  AS  DATETIME,
    CTE.[B] ,
    CTE.[A] 
          
    FROM CTE      
    RIGHT JOIN ctmax [CTE3]  ON  cte.DATETIME=[CTE3].last
     ORDER BY DATETIME  for json path

My Result :

[{"DATETIME":1628424359816,"B":35,"A":1},{"DATETIME":1628424380816,"B":63,"A":1}]

db<>fiddle here

2
1

The JSON you asked for is not valid. However, it looks like you may want a pretty similar JSON

{
  "result": [
    {
      "B": [1628424359816, 35]
    },
    {
      "A": [1628424359816, 1]
    },
    {
      "B": [1628424380816, 63]
    },
    {
      "A": [1628424380816, 1]
    }
  ]
}

For this, you can unpivot the values, then conditionally add different JSON keys

;WITH CTE AS (         
    SELECT
      DATETIME,
      [B],
      [A], 
      ROW_NUMBER() OVER (ORDER BY datetime) - ROW_NUMBER() OVER (PARTITION BY dateadd(minute, 1 + (datediff(minute, 0, t.DATETIME) / 1 ) * 1, 0) ORDER BY t.DATETIME) AS RN
    FROM test t
    WHERE DATETIME >='2021-05-29' AND DATETIME <='2021-08-30'
), 
ctmax as (
    select
      MIN(DATETIME) AS [FIRST],
      Max(DATETIME) as [last],
      min(rn) as rown
    FROM cte
    group by RN
)
SELECT
  JSON_QUERY(CASE WHEN ab = 'B' THEN value END) AS B,
  JSON_QUERY(CASE WHEN ab = 'A' THEN value END) AS A
FROM CTE      
RIGHT JOIN ctmax [CTE3] ON cte.DATETIME = [CTE3].last
CROSS APPLY (VALUES
    ('B', CONCAT(N'[', DATEDIFF_BIG(MILLISECOND, '1970-01-01 00:00:00', CTE.DATETIME), N',', CTE.B, N']')),
    ('A', CONCAT(N'[', DATEDIFF_BIG(MILLISECOND, '1970-01-01 00:00:00', CTE.DATETIME), N',', CTE.A, N']'))
) v(ab, value)
for json path, ROOT('result')

db<>fiddle

I must say, most of the query, the CTE and grouping etc seem pointless, but I have not changed that

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.