SQL Server 2016 JSON: Select array of strings instead of array of objects

Building on Loui Bao and Roman's answer:

declare @t table ([value] nvarchar(max))insert into @t ([value]) values ('foo')insert into @t ([value]) values ('bar')insert into @t ([value]) values ('test')insert into @t ([value]) values ('ok')SELECT    JSON_QUERY((SELECT CONCAT('["',STRING_AGG([value], '","'),'"]') FROM @t)) As MuhArray    OtherValue,    AnotherValueFROM MyTableOValuesFOR JSON PATH

This is creating a JSON valid array of simple values and assigning it to the property MuhArray. The JSON output from this would be:

[{  MuhArray: ["foo", "bar", "test", "ok"],  OtherValue: "Value",  AnotherValue: "AnotherValue"}]

Where OtherValue and AnotherValue receive whatever corresponding values were in the table. With some fiddling you could also choose not to build this as a subquery in the select list, but as a simple join in the main query body. In my view, using subqueries in the select list can remove the need for the distinct keyword.

In AdventureWorks 2016 CTP3 JSON sample you can find a function that can clean array of key:value pairs and create array od values:

DROP FUNCTION IF EXISTS dbo.ufnToRawJsonArrayGOCREATE FUNCTION[dbo].[ufnToRawJsonArray](@json nvarchar(max), @key nvarchar(400)) returns nvarchar(max)AS BEGIN       declare @new nvarchar(max) = replace(@json, CONCAT('},{"', @key,'":'),',')       return '[' + substring(@new, 1 + (LEN(@key)+5), LEN(@new) -2 - (LEN(@key)+5)) + ']'END

Just provide result of your SELECT FOR JSON expression as @json parameter and name of the key that you want to remove as second parameter. Probably something like:

select dbo.ufnToRawJsonArray( (SELECT value FROM mytable for json path), 'value')

In SQL2017, use STRING_AGG instead of json. This function is the best for generating comma-separated lists of values.

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql

SELECT town, STRING_AGG (email, ';') AS emails FROM dbo.Employee GROUP BY town;