How to generate a JSON value array in Azure SQL-CodePudding

DECLARE @segArr NVARCHAR(max)

set @segArr = N'[1,2,3]'

DECLARE @segTb table (
    k int,
    v NVARCHAR(20)
);
insert into @segTb
VALUES
(0, 'a'),
(1, 'b'),
(2, 'c'),
(3, 'd'),
(4, 'e'),
(5, 'f');


select t.v from @segTb t
JOIN openjson(@segArr) a on a.[key] = t.k
for JSON auto;

I have a simple table with a key-value like structure and a JSON array that is a list of keys of the values I wanted.

The select statement can create the desired result, but the JSON format is wrong. It outputs an array of objects.

[
    {
        "v": "a"
    },
    {
        "v": "b"
    },
    {
        "v": "c"
    }
]

But what I needed is an array of direct values.

[ "a", "b", "c" ]

CodePudding user response：

You can use more conventional string manipulation methods to create JSON arrays in Azure SQL DB such as STRING_AGG which aggregates strings with the given separator (eg comma) and QUOTENAME which surrounds strings with the given quote character. A simple example:

SELECT QUOTENAME( STRING_AGG( QUOTENAME(v, '"' ), ',' ), '[' ) AS yourArray
FROM
    (
    SELECT t.v FROM @segTb t
        INNER JOIN OPENJSON(@segArr) a ON a.[key] = t.k
    ) x