如何用SQL Server存储过程生成带嵌套数组的酒店JSON数据？

完全可以通过SQL Server存储过程实现你需要的嵌套JSON结果。你之前的查询问题在于直接多表JOIN会产生笛卡尔积，且没有利用SQL Server的JSON嵌套语法来生成images和facilities数组。

解决方案：存储过程实现嵌套JSON #

以下是完整的存储过程代码，通过子查询+FOR JSON PATH来生成每个酒店对应的嵌套图片和设施数组：

CREATE PROCEDURE GetHotelsWithNestedData
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        h.ID AS HotelID,
        h.name,
        h.star_rating,
        h.Address,
        h.isRefundable,
        -- 生成嵌套的images数组
        (
            SELECT url, title, description
            FROM ImagesTable img
            WHERE img.HotelID = h.ID
            FOR JSON PATH
        ) AS images,
        -- 生成嵌套的facilities数组
        (
            SELECT facilityID, facilityName
            FROM FacilitiesTable fac
            WHERE fac.HotelID = h.ID
            FOR JSON PATH
        ) AS facilities
    FROM main_hotel_table h
    FOR JSON PATH, ROOT('hotels');
END

代码说明 #

1. 子查询生成嵌套数组：针对每个酒店，分别查询其关联的图片和设施数据，用FOR JSON PATH将结果转为JSON数组，直接作为主查询的字段值。
2. 避免笛卡尔积：这种写法不会像多表JOIN那样产生重复数据，每个酒店只返回一行，嵌套数组包含所有关联的子表数据。
3. 空数组处理：如果某个酒店没有图片或设施，对应的字段会返回null。如果需要返回空数组[]，可以用ISNULL(子查询, '[]')来处理，例如：

   ISNULL(
       (SELECT url, title, description FROM ImagesTable img WHERE img.HotelID = h.ID FOR JSON PATH),
       '[]'
   ) AS images
   

执行存储过程 #

调用存储过程即可获取预期的JSON结果：

EXEC GetHotelsWithNestedData;

生成的JSON结构示例（以hotelA为例）：

{
  "hotels": [
    {
      "HotelID": 1,
      "name": "hotelA",
      "star_rating": 5,
      "Address": "a",
      "isRefundable": true,
      "images": [
        {"url":"x","title":"a","description":"a"},
        {"url":"x","title":"b","description":"b"},
        {"url":"x","title":"c","description":"c"},
        {"url":"x","title":"d","description":"d"}
      ],
      "facilities": [
        {"facilityID":1,"facilityName":"free wifi"},
        {"facilityID":2,"facilityName":"free breakfast"},
        {"facilityID":3,"facilityName":"TV"}
      ]
    },
    // 其他酒店数据...
  ]
}

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