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?

Jolin

What I learn from this Q&A = SQL Server 2016 advertises JSON support but does not provide a straightforward way to query a table and return an array in JSON format. Why on Earth should we have to be building the array string manually? Ugh. – Tirza

Answer 1

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.

Maletta

Answer 2

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

Dionedionis

Answer 3

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;

Mutinous

This breaks if I do insert into @t ([value]) values ('test " , '' '), as while for json path handles char escaping string_agg corrupts the output. This is only faster if you're sure the source doesn't contain any chars that need escaping. – Discourse

STRING_ESCAPE to help with that. learn.microsoft.com/en-us/sql/t-sql/functions/… – Mutinous

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"]

Kb

Answer 5

Use the following code with any versions from Sql Server 2016

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 @values nvarchar(max) = N'[]'; 
SELECT @values = JSON_MODIFY(@values,'append $',[value]) 
FROM @t SELECT @values

Slippy

You can just do ` DECLARE @values nvarchar(max) = N'[]'; SELECT @values = JSON_MODIFY(@values,'append $',[value]) FROM @t SELECT @values` actually – Luciano

Answer 6

Yet another solution

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('[' + STUFF((SELECT ',"' +  [value] + '"' from  @T FOR XML PATH('')), 1, 1, '')+']')

Returns:

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

Ambassadress

solution that doesn't need a procedure/function – Humpback

This is what I needed, and the JSON_QUERY is needed to output JSON object, otherwise it outputs an escaped string containing JSON – Musicale

Answer 7

This should get you started. 2016 has a few ways of working with JSON. This method defines the JSON string, pulls the values out of it, then uses XML tricks to get the multiple rows into one row.

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

Oriente