SQL Server 2016 JSON: selecting an array of strings instead of an array of objects

Question

I am new to JSON in SQL Server and cannot 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

Returns an array of objects:

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

I would like him to return:

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

Can this be done?

Answer 1

In the CTP3 JSON example from AdventureWorks 2016, you can find a function that can clear an array of key: value pairs and create od array 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 specify the result of the SELECT FOR JSON expression as the @json parameter and the name of the key you want to remove as the second parameter. Perhaps something like:

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

Answer 2

. 2016 JSON. JSON, , XML-, .

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

DECLARE @JSON NVARCHAR(MAX) = (
        SELECT *
        FROM @T
        FOR JSON PATH
        );

WITH cte
AS (
    SELECT *
    FROM OPENJSON(@json) WITH (NAME VARCHAR(10) '$.value')
    )
SELECT QUOTENAME(left(names, LEN(names) - 1) )AS names
FROM (
    SELECT DISTINCT (
            SELECT QUOTENAME(NAME,'"') + ',' AS [text()]
            FROM cte
            FOR XML PATH('')
            ) names
    FROM cte
    ) x

Answer 3

In SQL2017, use STRING_AGG instead of using json. This function is best suited 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;