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?

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. — youcantryreachingme, Jan 21 '21 at 22:33

score 12 · Answer 1 · answered Jan 03 '19 at 17:06

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.

score 8 · Answer 2 · answered Oct 09 '16 at 20:41

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

score 8 · Answer 3 · answered Mar 08 '18 at 05:18

8

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;

answered Mar 08 '18 at 05:18

Roman Polunin

53
3
12

2

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. – Keith Sep 10 '18 at 18:13
2

STRING_ESCAPE to help with that. https://learn.microsoft.com/en-us/sql/t-sql/functions/string-escape-transact-sql?view=sql-server-2017 – Roman Polunin Sep 13 '19 at 06:23

score 5 · Answer 4 · answered Aug 09 '18 at 13:14

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

score 2 · Answer 5 · 2020-07-09T06:21:55.247

2

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

edited Jul 09 '20 at 06:21

answered Jun 17 '20 at 15:00

2

You can just do ` DECLARE @values nvarchar(max) = N'[]'; SELECT @values = JSON_MODIFY(@values,'append $',[value]) FROM @t SELECT @values` actually – Ilya Chernomordik Jul 07 '20 at 12:54

Onur · Answer 6 · 2021-06-16T20:24:44.890

1

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

edited Jun 16 '21 at 20:24

answered Jun 16 '21 at 20:18

Onur

852
9
18

solution that doesn't need a procedure/function – complete_stranger Apr 03 '23 at 12:25

score 0 · Answer 7 · answered Sep 12 '16 at 15:15

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

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

7 Answers7

Linked