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

Question

I am new to JSON in SQL Server and can't figure out how to return a simple array of strings:

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 [value]
FROM @T
FOR JSON PATH

This returns an array of objects:

[{"value":"foo"},{"value":"bar"},{"value":"test"},{"value":"ok"}]

I would like it to return:

["foo","bar","test","ok"]

Can this even be done?

Answer 1

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.ufnToRawJsonArray
GO
CREATE 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')