如何将SQL Server 2017中的表格数据转换为指定格式的JSON？

注意事项 #

你要求的JSON格式存在重复键名（顶级对象中多次出现Team、Drivers、Wins），这不符合JSON规范，大部分JSON解析工具会忽略重复键或抛出错误。如果可以接受规范格式，建议使用数组包裹每个车队的对象（下文会提供两种方案）。

方案1：生成你指定的非规范JSON格式 #

通过字符串拼接的方式实现，步骤如下：

1. 首先创建测试表并插入数据（如果已有表可跳过）

CREATE TABLE RaceResults (
    Team VARCHAR(50),
    Drivers VARCHAR(50),
    Wins VARCHAR(50)
);

INSERT INTO RaceResults VALUES
('Red Bull', 'Max Verstappen', 'Australia'),
('Red Bull', 'Sergio Perez', 'Azerbaijan'),
('Ferrari', 'Charles Leclerc', 'Spain'),
('Ferrari', 'Carlos Sainz', 'Bahrain');

2. 执行以下SQL生成目标格式

DECLARE @JsonResult NVARCHAR(MAX);

SELECT @JsonResult = CONCAT(
    '{', CHAR(13), CHAR(10),
    STRING_AGG(TeamJson, ',' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)), CHAR(13), CHAR(10),
    '}'
)
FROM (
    SELECT 
        CONCAT(
            '       "Team": [', CHAR(13), CHAR(10),
            '                 "', Team, '"', CHAR(13), CHAR(10),
            '               ],', CHAR(13), CHAR(10),
            '       "Drivers": [', CHAR(13), CHAR(10),
            '                 "', STRING_AGG(Drivers, '",' + CHAR(13) + CHAR(10) + '                 "'), '"', CHAR(13), CHAR(10),
            '                  ],', CHAR(13), CHAR(10),
            '       "Wins":    [', CHAR(13), CHAR(10),
            '                 "', STRING_AGG(Wins, '",' + CHAR(13) + CHAR(10) + '                 "'), '"', CHAR(13), CHAR(10),
            '                  ]'
        ) AS TeamJson
    FROM RaceResults
    GROUP BY Team
) AS Teams;

PRINT @JsonResult;

执行后将输出你要求的JSON格式。

方案2：生成符合JSON规范的格式（推荐） #

使用SQL Server自带的FOR JSON PATH功能生成规范JSON，格式为数组包裹每个车队对象：

SELECT 
    Team,
    (SELECT Drivers FROM RaceResults r2 WHERE r2.Team = r1.Team FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS Drivers,
    (SELECT Wins FROM RaceResults r2 WHERE r2.Team = r1.Team FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS Wins
FROM RaceResults r1
GROUP BY Team
FOR JSON PATH;

输出结果如下：

[
    {
        "Team": "Red Bull",
        "Drivers": ["Max Verstappen", "Sergio Perez"],
        "Wins": ["Australia", "Azerbaijan"]
    },
    {
        "Team": "Ferrari",
        "Drivers": ["Charles Leclerc", "Carlos Sainz"],
        "Wins": ["Spain", "Bahrain"]
    }
]

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