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

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

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