如何将SQL Server表按日期分组转换为指定格式的JSON？

方法一：使用STRING_AGG（SQL Server 2017及以上版本） #

这种方法通过字符串聚合构造JSON数组，再用JSON_QUERY确保结果被识别为JSON类型，避免不必要的转义：

DROP TABLE IF EXISTS #tmp
CREATE TABLE #tmp(AnalyticGoodsT CHAR(6), date SMALLDATETIME)

INSERT INTO #tmp(AnalyticGoodsT, date)
VALUES ('000001', '20230424'), ('000002', '20230424'), ('000003', '20230424'), ('000004', '20230424')

-- 生成目标JSON
SELECT
    CONVERT(VARCHAR, date, 104) AS Date,
    JSON_QUERY('["' + STRING_AGG(QUOTENAME(AnalyticGoodsT, '"'), '","') + '"]') AS bk
FROM #tmp
GROUP BY date
FOR JSON PATH

方法二：使用子查询+FOR JSON PATH（兼容更早版本） #

通过子查询生成纯值数组，再用JSON_QUERY处理JSON类型，保证输出格式正确：

SELECT
    CONVERT(VARCHAR, date, 104) AS Date,
    JSON_QUERY((
        SELECT AnalyticGoodsT AS [value]
        FROM #tmp a2
        WHERE a2.date = a1.date
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    )) AS bk
FROM #tmp a1
GROUP BY date
FOR JSON PATH

原方法的问题 #

你之前的实现通过REPLACE修改JSON字符串，这种方式存在明显缺陷：

• 如果AnalyticGoodsT的值包含{"id":或"}这类字符串，会直接破坏JSON格式
• 依赖子查询生成的JSON结构细节，一旦SQL Server的JSON生成逻辑变更，代码会失效
• 无法保证输出JSON的合法性，容易出现语法错误

内容的提问来源于stack exchange，提问作者Вовжан