SQL Server:使用分组依据查询到JSON对象的列 [英] SQL Server : query columns to JSON object with group by

问题描述

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

I have a table with 3 columns, I want to query that table such that the result will be a JSON object.

样本数据如下:

 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行".我想要的是每组一行.组是此处的ID，值组合.我对JSON对象的经验很少(可以从此查询中看到)，因此将不胜感激.

But that returns 1 JSON 'row'. What I want, is one row per group. Group being here ID, Value combination. I have little experience with JSON objects (which is probably visible from this query), so help would be appreciated.

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

The desired output (but then as JSON per row):

 --------------------------------------------------
|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|

提前谢谢！

推荐答案

这有效(在SQL Server 2017中，其中STRING_AGG可用)，但是非常笨拙.我不确定还有没有更优雅的方法.

This works (in SQL Server 2017, where STRING_AGG is available), but is quite clumsy. I'm not sure there's not a more elegant way.

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):

For SQL Server 2016 (which has no STRING_AGG, or STRING_ESCAPE for that matter):

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 ...

Even less elegant, but you take what you can get. At least we're not concatenating with FOR XML...

这篇关于SQL Server:使用分组依据查询到JSON对象的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！