如何在Azure SQL中生成纯值类型的JSON数组而非对象数组

问题原因 #

FOR JSON语法默认输出带键名的对象数组，SQL Server没有直接输出纯值数组的原生参数，需要手动构造结构。

解决方案 #

方案1（SQL Server 2017及以上版本，推荐）

使用STRING_AGG拼接数组元素，配合STRING_ESCAPE处理特殊字符，最后用JSON_QUERY标记结果为合法JSON：

SELECT JSON_QUERY(
    '[' + STRING_AGG('"' + STRING_ESCAPE(t.v, 'json') + '"', ',') + ']'
) AS result
FROM @segTb t
JOIN OPENJSON(@segArr) a ON a.[key] = t.k

运行后直接返回你需要的["a","b","c"]格式结果。

方案2（兼容SQL Server 2016版本）

如果数据库版本不支持STRING_AGG，可以用FOR XML PATH实现字符串拼接：

SELECT JSON_QUERY(
    '[' + STUFF(
        (
            SELECT ',"' + STRING_ESCAPE(t.v, 'json') + '"'
            FROM @segTb t
            JOIN OPENJSON(@segArr) a ON a.[key] = t.k
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'),
    1, 1, '') + ']'
) AS result

注意：你当前的JOIN条件匹配的是JSON数组的索引位置而非数组存储的数值，如果需要匹配数组里实际存储的键值，把条件改为a.[value] = t.k即可。

内容的提问来源于stack exchange，提问作者David S.