SQL Server : query columns to JSON object with group by

Question

我有一个包含 3 列的表，我想查询该表，结果将是一个 JSON 对象。

示例数据如下所示:

 CREATE TABLE #Test (ValueV INT, KEYS NVARCHAR (100), ID INT) 

 INSERT INTO #Test 
 VALUES (1, N'ChangeAdress 19 - 21', 200),
        (1, N'ChangeAdress 20 - 22', 200),
        (1, N'ChangeAdress 22 - 24', 300),
        (1, N'ChangeAdress 23 - 25', 300),
        (2, N'ChangeAdress 24 - 26', 400),
        (2, N'ChangeAdress 25 - 27', 400),
        (3, N'ChangeAdress 26 - 28', 400),
        (3, N'ChangeAdress 27 - 29', 400)

 SELECT * FROM #Test

我的查询尝试:

 SELECT ID, Keys, ValueV
 FROM #Test  
 GROUP BY ID, keys, ValueV
 FOR JSON AUTO

但这会返回 1 个 JSON“行”。我想要的是每组一行。这里的Group就是ID，Value的组合。我对 JSON 对象的经验很少(这可能从此查询中可见)，因此我们将不胜感激。

所需的输出(但每行为 JSON):

 --------------------------------------------------
|200, 1, ChangeAdress 19 - 21, ChangeAdress 20 - 22|
|300, 1, ChangeAdress 22 - 24, ChangeAdress 23 - 25|
|400, 2, ChangeAdress 24 - 26, ChangeAdress 25 - 27|
|400, 3, ChangeAdress 26 - 28, ChangeAdress 27 - 29|

提前致谢!

Answer 1

这可行(在 SQL Server 2017 中，可以使用 STRING_AGG)，但相当笨拙。我不确定还有没有更优雅的方法。

SELECT (
    SELECT 
       ID, 
       ValueV, 
       Keys = JSON_QUERY('["' + STRING_AGG(STRING_ESCAPE(Keys, 'json'), '","') + '"]')
    FOR JSON PATH
)
FROM #Test 
GROUP BY ID, ValueV

对于 SQL Server 2016(没有 STRING_AGG 或 STRING_ESCAPE):

SELECT (
    SELECT ID, ValueV, Keys = JSON_QUERY(REPLACE(REPLACE(
        (
            SELECT Keys 
            FROM #Test t2 WHERE t2.ID = t1.ID AND t2.ValueV = t1.ValueV 
            FOR JSON PATH
        ),
        '{"Keys":', ''),
        '}', ''))
    FOR JSON PATH
)
FROM #Test t1
GROUP BY ID, ValueV

更不优雅，但你能得到什么就拿什么。至少我们没有与 FOR XML 连接...