SQL Server select to json, column name as key and unique values of column as list value

Question

For an SQL table like this

Column A	Column B
Xxx	Yyyy
Xxx	zzzz
Rrr	Yyyy

I want to retrieve a JSON result like this

{"Column A": ["Xxx", "Rrr"], 
 "Column B": ["Yyyyy", "zzzz"]}

The unique/distinct values of each column should be an array element added as value to the JSON key.

Edit: Note : SQL Server version is prior to 2017.

Edit 2:

Distinct values for each column in table should be selected.
The selected distinct value should be converted into a list.
All the column names should be converted as JSON key and the list values should become JSON value.

This would require a bit of string manipulation. Take a peek at stackoverflow.com/questions/70511213/… or stackoverflow.com/questions/68154957/… — John Cappelletti
– John Cappelletti, Commented Nov 22, 2022 at 17:55

buddemat · Accepted Answer · 2022-11-23 11:17:25Z

You can adopt this SO answer to your needs.

SELECT "Column A" = JSON_QUERY('["'+STUFF((SELECT DISTINCT CONCAT('","',STRING_ESCAPE("Column A",'json'))  
                                             FROM  mytable
                                              FOR XML PATH ('')),1,3,'')+'"]'),
       "Column B" = JSON_QUERY('["'+STUFF((SELECT DISTINCT CONCAT('","',STRING_ESCAPE("Column B",'json'))  
                                             FROM  mytable
                                              FOR XML PATH ('')),1,3,'')+'"]')
   FOR JSON PATH, Without_Array_Wrapper

That yields the result

JSON
{"Column A":["Rrr","Xxx"],"Column B":["Yyyy","zzzz"]}

Also see it in action in this db<>fiddle (SQL Server 2016).

Detailed explanation, from the inside out:

STRING_ESCAPE ensures that the selected values are properly escaped to be valid JSON values.
FOR XML PATH returns the results of the inner query as XML instead of a rowset.
DISTINCT makes sure you only have each element once.
CONCAT joins commas and double quotes.
STUFF removes the unnecessary leading comma that was added by CONCAT.
Outer double quotes and brackets are added.
JSON_QUERY yields an object from the constructed JSON string.
Finally, the outer FOR JSON PATH joins the constructed columns into one JSON.

See individual steps in this db<>fiddle.

You could also use dynamic SQL, as in this SO answer to do this automatically for all columns in your table:

DECLARE @sql VARCHAR(max) = ''
DECLARE @tablename as VARCHAR(255) = 'mytable'

SELECT @sql = @sql + ',[' + c.name + '] = (SELECT JSON_QUERY(''["''+STUFF((SELECT DISTINCT concat(''","'',STRING_ESCAPE([' + c.name + '],''json''))  
                                             FROM  ' + t.name + '
                                              FOR XML PATH ('''')),1,3,'''')+''"]''))' 
  FROM sys.columns c
  JOIN sys.tables t on c.object_id = t.object_id
 WHERE t.name = @tablename
SELECT @sql = 'SELECT ' + STUFF(@sql, 1, 1, '') + ' FOR JSON PATH, Without_Array_Wrapper'
  
EXEC (@sql)

See this db<>fiddle for an example with five source columns.

Collectives™ on Stack Overflow

SQL Server select to json, column name as key and unique values of column as list value

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related