Simple JSON array from Single SQL Server table column

user_9212855 0
Viewed 754

All I'm trying to do is take a column and create a simple JSON ARRAY from it. FOR JSON is driving me nuts. The below code ...

DECLARE @t TABLE (name VARCHAR (64))

INSERT INTO @t 
    SELECT value 
    FROM OPENJSON ('["b","c"]', '$')

SELECT name 
FROM @t 
FOR JSON AUTO

... yields ...

[{"name":"b"},{"name":"c"}]

... but what I want is ...

[ "b", "c" ]

My problem is I have to say SELECT and the moment I do that the array spewed out has a Name-Value pair in each JSON ARRAY element. I'm thinking this should be simple, or am I just going about it the wrong way and need to use some STRING functions? Ultimately I want to assign the result to a VARCHAR anyways.

If question seems silly, want to clarify I'm getting lots of JSON arrays which I want to convert to single column tables and do SET operations like INTERSECT, UNION and EXCEPT and ultimately land with a final single column table which I then want to return as a simple JSON ARRAY.

sql-server json
1 Answers

A simple solution (For SQL < 2017, the STRING_AGG is better) but be careful with SQL Injection

Create this proc:

CREATE OR ALTER PROC JSON_ARRAY(@TBL VARCHAR(100), @COL VARCHAR(100), @OUTPUT NVARCHAR(MAX)=NULL OUT) AS BEGIN
    SET @OUTPUT='
    SELECT ' + IIF(@OUTPUT IS NULL, '@OUTPUT', 'JSON_ARRAY') + '=''['' + STUFF((
        SELECT
            '', "'' + LTRIM(' + @COL + ') + ''"''
        FROM ' + @TBL + '
            FOR XML PATH('''')
        ), 1, 2, '''') + '']'''
    --PRINT @OUTPUT
    EXEC SP_EXECUTESQL @OUTPUT, N'@OUTPUT NVARCHAR(MAX) OUT', @OUTPUT OUT
END

Then use it like theese examples:

EXEC JSON_ARRAY 'SYS.VIEWS', NAME, 1 --> Execute the SELECT statement



DECLARE @array VARCHAR(MAX)
EXEC JSON_ARRAY 'SYS.VIEWS', 'NAME', @array OUT
PRINT @array --> You have the array in this variable
user_125679420
Related