SQL Server如何按年份分组生成对应唯一品牌数组的JSON

要生成以唯一年份为键、对应年份下唯一品牌数组的JSON结构，可以通过CTE分组聚合+字符串拼接的方式实现，以下是针对SQL Server的具体查询方案：

基础版本（兼容SQL Server 2017+） #

假设你的表名为VehicleData，包含Year（年份字段）和Make（品牌字段），替换成你的实际表名和字段即可：

-- 按年份分组，聚合去重后的品牌为字符串
WITH YearlyMakes AS (
    SELECT
        [Year],
        -- 用双引号分隔品牌，方便后续组装JSON数组
        STRING_AGG(DISTINCT Make, '","') AS MakesList
    FROM VehicleData
    GROUP BY [Year]
)
-- 拼接成目标JSON格式
SELECT 
    CONCAT(
        '{',
        STRING_AGG(
            CONCAT('"', [Year], '": ["', MakesList, '"]'),
            ', '
        ),
        '}'
    ) AS YearlyMakeJson
FROM YearlyMakes

简洁版本（SQL Server 2022+） #

如果使用的是SQL Server 2022及以上版本，可以用JSON_ARRAYAGG直接生成JSON数组，无需手动拼接：

-- 按年份分组，直接生成品牌数组
WITH YearlyMakes AS (
    SELECT
        [Year],
        JSON_ARRAYAGG(DISTINCT Make) AS MakesArray
    FROM VehicleData
    GROUP BY [Year]
)
-- 拼接成目标JSON对象
SELECT 
    CONCAT(
        '{',
        STRING_AGG(
            CONCAT('"', [Year], '": ', MakesArray),
            ', '
        ),
        '}'
    ) AS YearlyMakeJson
FROM YearlyMakes

处理特殊字符的版本 #

如果品牌或年份字段包含双引号等JSON特殊字符，需要用STRING_ESCAPE转义，避免JSON格式错误：

WITH YearlyMakes AS (
    SELECT
        [Year],
        STRING_AGG(DISTINCT STRING_ESCAPE(Make, 'json'), '","') AS MakesList
    FROM VehicleData
    GROUP BY [Year]
)
SELECT 
    CONCAT(
        '{',
        STRING_AGG(
            CONCAT('"', STRING_ESCAPE([Year], 'json'), '": ["', MakesList, '"]'),
            ', '
        ),
        '}'
    ) AS YearlyMakeJson
FROM YearlyMakes

说明 #

• 上述查询会直接输出你需要的JSON字符串格式，不需要额外的FOR JSON PATH嵌套（因为目标结构是动态键，FOR JSON默认生成的是数组结构，无法直接生成动态键的对象，因此用字符串拼接更直接）。
• 如果你的数据库是其他类型（比如MySQL、PostgreSQL），语法会有差异，但核心思路都是先分组聚合，再拼接成目标JSON结构。

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