SQL Server : query columns to JSON object with gro

问题:

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

Sample data looks like this:

 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

My query attempt:

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

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.

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|

Thanks in advance!

回答1:

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

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

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

回答2:

Try this:

SELECT (SELECT [ID], [Keys], [ValueV]  FOR JSON PATH)
FROM #Test 
GROUP BY ID, keys, ValueV

or this:

SELECT (SELECT [ID], [Keys], [ValueV]  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
FROM #Test 
GROUP BY ID, keys, ValueV

回答3:

My two cents:

Interesting that you want valid individual JSON rows and not one single JSON string. Anyhow, here are some alternate answers, although the accepted answer is the best one.

-- 100% hardcoded yourself. Pre SQL Server 2016 
SELECT '[{"ID":' + convert(nvarchar(4),T1.[ID]) + ',"ValueV":' + convert(nvarchar(4),T1.[ValueV]) + ',"Keys":["' + T1.[Keys] + '","' + T2.[Keys] + '"]}]' AS [keys]
FROM #Test AS T1 INNER JOIN #Test T2 ON t2.ID = t1.ID AND t2.ValueV = t1.ValueV AND t2.keys > t1.keys 

Or:

-- Use the OPENJSON to output your results as a dataset and not just a single row. I've removed the escape character back slashes to match the accepted answers output  
    SELECT 
     '[' + REPLACE((REPLACE((REPLACE([value], '\','')),':"[',':[')),']"}',']}') + ']'
    FROM OPENJSON(
    (SELECT T1.[ID],T1.[ValueV], '["' + T1.[Keys] + '","' + T2.[Keys] + '"]' AS [keys]
    FROM #Test AS T1 INNER JOIN #Test T2 ON t2.ID = t1.ID AND t2.ValueV = t1.ValueV AND t2.keys > t1.keys 
    FOR JSON PATH))

Or:

-- This is a lot cleaner for an array of values in pairs. Again using OPENJSON to output your desired result.  
select 
'[' + [Value] + ']' FROM OPENJSON(
(select T1.[ID], T1.[ValueV], JSON_MODIFY(JSON_MODIFY('[]','append lax $',t0.keys),'append lax $',t1.keys) as keys
FROM #Test AS T0 inner join #Test as t1
on  t0.ID = t1.ID AND t0.ValueV = t1.ValueV AND t0.keys < t1.keys
FOR JSON path))

Just quick a note that JSON itself is not an object, but just a string of named value pairs, valid JavaScript but a subset nonetheless, it looks completely different when for instance you want a nested Javascript object (If JS is your intended data destination, I'm assuming here). The JSON function's great for quickly pulling off data for transfer, but when you want a nested output or grouping the dataset for an array of values not objects, it can become quite tricky. Personally, since it's a string, for more complicated stuff I just build it myself. Hope that's a different take.