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 SQL2017, use STRING_AGG instead of json. This function is the best for generating comma-separated lists of values.

https://learn.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;

Answer 2

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,
    AnotherValue
FROM MyTableOValues
FOR 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.

Answer 3

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

Answer 4

Building on top of 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 concat('[', string_agg(concat('"', [value], '"'), ','), ']')
from @t

output:

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