如何在SQL中将多表查询结果转换为无数组嵌套的子JSON

我明白你遇到的困扰——想要生成完全符合预期结构的JSON，让Country和State作为独立子对象，而不是被数组[]包裹。之前用FOR JSON AUTO或带WITHOUT_ARRAY_WRAPPER的PATH方式没得到理想结果，主要是因为自动生成的JSON会根据表关联关系默认嵌套数组，咱们可以通过手动构造每个子对象的结构来解决这个问题。

方案一：使用JSON_OBJECT直接构造子对象（SQL Server 2022+） #

如果你的SQL Server版本是2022或更高，JSON_OBJECT函数可以直接帮你构造单个JSON对象，非常直观：

SELECT
  ad.FirstName AS firstName,
  ad.LastName AS lastName,
  JSON_OBJECT(
    'id', country.Id,
    'name', country.Name
  ) AS Country,
  JSON_OBJECT(
    'id', sp.Id,
    'name', sp.Name
  ) AS States
FROM [order] ord
JOIN AddressTable ad ON ord.AddressId = ad.Id
LEFT JOIN CountryTable country ON ad.countryId = country.Id
LEFT JOIN StateTable sp ON ad.stateId = sp.Id
WHERE ord.Id = 1 -- 按需过滤订单ID
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

方案二：子查询+FOR JSON PATH, WITHOUT_ARRAY_WRAPPER（兼容低版本SQL Server） #

如果是SQL Server 2017及以下版本，没有JSON_OBJECT，可以用子查询单独生成每个子对象，同时通过WITHOUT_ARRAY_WRAPPER去掉数组包裹：

SELECT
  ad.FirstName AS firstName,
  ad.LastName AS lastName,
  (
    SELECT country.Id AS id, country.Name AS name
    FROM CountryTable country
    WHERE country.Id = ad.countryId
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ) AS Country,
  (
    SELECT sp.Id AS id, sp.Name AS name
    FROM StateTable sp
    WHERE sp.Id = ad.stateId
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ) AS States
FROM [order] ord
JOIN AddressTable ad ON ord.AddressId = ad.Id
WHERE ord.Id = 1
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

为什么这个方法有效？ #

• 两种方案都避免了多表JOIN后自动生成的数组嵌套：第一种直接构造单个对象，第二种通过子查询只返回单个关联行，再用WITHOUT_ARRAY_WRAPPER确保输出是单个对象而非数组。
• 最终生成的JSON结构完全符合你的预期：

{
  "firstName": "John",
  "lastName": "cena",
  "Country" : { "id": "5", "name": "usa" },
  "States" : { "id": "7", "name": "DC" }
}

注意事项 #

• 确保每个Address对应的Country和State是唯一的（根据你的表结构，一个Address关联一个Country和一个State，所以子查询只会返回一行，不会生成数组）。
• 如果需要返回多个订单的JSON，去掉外层的WITHOUT_ARRAY_WRAPPER，就能得到包含多个订单对象的数组。

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