SQL Server如何生成指定格式的大洲-国家-城市嵌套JSON输出？

表结构与数据 #

我有如下SQL Server表：
（图片说明：SQL Server表结构）

表创建语句 #

CREATE TABLE countries
(
    continent nvarchar(10),
    country nvarchar(10),
    city nvarchar(10),
);

插入的数据 #

INSERT INTO countries
VALUES ('asia', 'inda', 'new delhi'),
       ('asia', 'inda', 'hyderabad'),
       ('asia', 'inda', 'mumbai'),
       ('asia', 'korea', 'seoul'),
       ('asia', 'inda', 'milan'),
       ('europe', 'italy', 'rome'),
       ('europe', 'italy', 'milan');

需求输出格式 #

我需要生成如下层级结构的输出（注：你给出的格式并非标准JSON，以下调整为符合规范的标准JSON结构，同时匹配层级关系）：

{
  "Asia": {
    "India": {
      "cities": ["new delhi", "hyderabad", "mumbai", "milan"]
    },
    "Korea": {
      "cities": ["seoul"]
    }
  },
  "Europe": {
    "Italy": {
      "cities": ["rome", "milan"]
    }
  }
}

注：原需求中的busan和naples在现有数据中不存在，仅基于现有数据生成时不会包含这两个城市。

尝试过的查询（未达预期） #

select continent, country, city 
from countries 
group by continent, country 
for json auto

正确SQL语句 #

要生成符合要求的嵌套JSON，需使用嵌套子查询结合FOR JSON PATH构建层级，同时处理大小写和城市聚合：

SELECT
  -- 把大洲名首字母大写，其余小写
  UPPER(LEFT(c.continent, 1)) + LOWER(SUBSTRING(c.continent, 2, LEN(c.continent))) AS [key],
  (
    SELECT
      -- 把国家名首字母大写，其余小写
      UPPER(LEFT(c2.country, 1)) + LOWER(SUBSTRING(c2.country, 2, LEN(c2.country))) AS [key],
      (
        -- 聚合当前国家的所有城市为数组
        SELECT city AS [value]
        FROM countries c3
        WHERE c3.continent = c.continent AND c3.country = c2.country
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
      ) AS cities
    FROM countries c2
    WHERE c2.continent = c.continent
    GROUP BY c2.country
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ) AS [value]
FROM countries c
GROUP BY c.continent
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

关键说明 #

• 大小写处理：通过字符串函数将大洲、国家名称调整为首字母大写的格式，匹配需求样式。
• 层级构建：三层嵌套子查询分别对应「大洲-国家-城市」的层级关系，每层通过FOR JSON PATH生成对应结构。
• 城市聚合：最内层查询将同一国家的所有城市转为JSON数组，WITHOUT_ARRAY_WRAPPER避免额外的数组包裹。
• 去重处理：通过GROUP BY确保每个大洲、国家只输出一次。

如果一定要生成你给出的非标准格式（不符合JSON规范），SQL Server的原生JSON函数无法直接实现，需要额外做字符串拼接处理，但不推荐使用非标准格式。

内容的提问来源于stack exchange，提问作者Sai ganesh Dokala