Simple JSON array from Single SQL Server table column

Question

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.

If you just want a single output that is a string like that, why not something simple like SELECT '[ "' + string_agg(name, '", "') + '" ]' FROM @t;? — ZLK
– ZLK, Commented Nov 5, 2018 at 23:06
@ZLK yeah, that's what I ended up doing but used XML PATH with Stuff. Lotsa examples on the web. Thanks! — griftopia
– griftopia, Commented Nov 7, 2018 at 0:10

Erick de Vathaire · Accepted Answer · 2021-12-28 00:30:40Z

0

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

answered Dec 28, 2021 at 0:30

Erick de Vathaire

1732 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Simple JSON array from Single SQL Server table column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related