如何獲取結果值 JSON 並發送結果格式 JSON

我有結果 JSON 查詢，但我需要這樣的結果：

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

我的查詢：

         ;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

我的結果：

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

db<> 在這裡擺弄

您要求的 JSON 無效。但是，看起來您可能想要一個非常相似的 JSON

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

為此，您可以取消透視值，然後有條件地添加不同的 JSON 鍵

;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<>小提琴

我必須說，大多數查詢、CTE 和分組等似乎毫無意義，但我沒有改變它

引用自：https://dba.stackexchange.com/questions/300148