如何将SQL查询返回的单列结果转换为纯值JSON数组

解决方案 #

方案1：兼容 SQL Server 2016 及以上版本（推荐，无需额外函数） #

调整查询写法，将PageID列的别名设为空字符串即可，修改后完整SQL如下：

SELECT (
    SELECT CP.PageID AS ''
    FROM CommClient.dbo.ClientPage AS CP
    INNER JOIN CommApp.dbo.Page as P ON CP.PageID = P.PageID 
    INNER JOIN CommApp.dbo.PageGroup as PG ON P.PageGroupID = PG.PageGroupID
    LEFT JOIN (
       SELECT DISTINCT ParentPageID FROM [CommApp].[dbo].[Page]
    ) AS PP 
    ON P.PageID = PP.ParentPageID
    WHERE ClientID = 102 AND P.ApplicationID = 4
    ORDER BY P.pageID
    FOR JSON PATH
) AS PageIdArray

原理是当查询列的别名为空字符串时，FOR JSON PATH不会生成对应的JSON键名，最终输出就是你需要的纯数值数组格式。

方案2：SQL Server 2017 及以上版本（写法更直观） #

使用STRING_AGG函数拼接所有值后套上数组括号：

SELECT JSON_QUERY(CONCAT('[', STRING_AGG(CP.PageID, ','), ']')) AS PageIdArray
FROM CommClient.dbo.ClientPage AS CP
INNER JOIN CommApp.dbo.Page as P ON CP.PageID = P.PageID 
INNER JOIN CommApp.dbo.PageGroup as PG ON P.PageGroupID = PG.PageGroupID
LEFT JOIN (
   SELECT DISTINCT ParentPageID FROM [CommApp].[dbo].[Page]
) AS PP 
ON P.PageID = PP.ParentPageID
WHERE ClientID = 102 AND P.ApplicationID = 4

这里加JSON_QUERY是为了保证返回结果被识别为JSON格式而非普通字符串，避免后续处理时多转义一层引号。

额外说明 #

如果你后续需要查询字符串类型的列生成纯值数组，需要额外给每个值套上单引号并转义内部的引号，数值类型不需要做额外处理，以上写法可以直接复用。

内容的提问来源于stack exchange，提问作者Fabrice