如何编写SQL嵌套查询生成指定嵌套JSON格式？

需求说明 #

当同一个Booking_number对应多个Container_number时，将该Booking_number下的第一条记录作为根节点，其余记录放入根节点的Sub_Rows数组中；若Booking_number仅对应一条记录，则直接作为独立节点。

样本数据表 #

预期嵌套JSON格式 #

[{
    "Sales_Order": 17493993,
    "PO_number": "4338_A",
    "Booking_number": "MXO0583354",
    "Container_number": "CAIU9661214",
    "Seal_number": "H4131037",
    "ETD1": "30/06/2022",
    "ETA1": "04/08/2022",
    "Country": "Jordan",
    "Sub_Rows": [{
        "Sales_Order": 17493996,
        "PO_number": "4338_A",
        "Booking_number": "MXO0583354",
        "Container_number": "CMAU4496275",
        "Seal_number": "H4131039",
        "ETD1": "30/06/2022",
        "ETA1": "04/08/2022",
        "Country": "Jordan"
        }]
    },{
    "Sales_Order": 17699594,
    "PO_number": "4355",
    "Booking_number": "190VC0059204",
    "Container_number": "MSMU8090753",
    "Seal_number": "FJ14673712",
    "ETD1": "02/07/2022",
    "ETA1": "11/08/2022",
    "Country": "Jordan",
    "Sub_Rows": [{
        "Sales_Order": 17699593,
        "PO_number": "4355",
        "Booking_number": "190VC0059204",
        "Container_number": "MSDU7050180",
        "Seal_number": "FJ14673630",
        "ETD1": "02/07/2022",
        "ETA1": "11/08/2022",
        "Country": "Jordan"
        },{
        "Sales_Order": 17699591,
        "PO_number": "4355",
        "Booking_number": "190VC0059204",
        "Container_number": "CAIU7594190",
        "Seal_number": "FJ14673612",
        "ETD1": "02/07/2022",
        "ETA1": "11/08/2022",
        "Country": "Jordan"
        }]
    },{
    "Sales_Order": 18193374,
    "PO_number": "Order_for_September_2",
    "Booking_number": "190VC0059041",
    "Container_number": "MSMU8133238",
    "Seal_number": "FJ14673638",
    "ETD1": "06/07/2022",
    "ETA1": "19/08/2022",
    "Country": "Latvia"
    }
]

原查询问题分析 #

原查询存在两个核心问题：

1. GROUP BY包含了所有列，导致每条记录都会被视为独立分组，最终生成的JSON会包含所有原始记录，而非按Booking_number合并的结构。
2. 子查询返回了当前Booking_number下的所有记录（包括根节点自身），不符合仅将其余记录放入Sub_Rows的要求。

解决方案SQL #

WITH RankedOrders AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Booking_number ORDER BY Sales_Order) AS RowNum
    FROM [DMS_Backend].[dbo].[DMS_DEV]
)
SELECT 
    RO.Sales_Order,
    RO.PO_number,
    RO.Booking_number,
    RO.Container_number,
    RO.Seal_number,
    RO.ETD1,
    RO.ETA1,
    RO.Country,
    Sub_Rows = (
        SELECT 
            Sales_Order,
            PO_number,
            Booking_number,
            Container_number,
            Seal_number,
            ETD1,
            ETA1,
            Country
        FROM RankedOrders
        WHERE Booking_number = RO.Booking_number AND RowNum > 1
        FOR JSON PATH
    )
FROM RankedOrders RO
WHERE RO.RowNum = 1
FOR JSON PATH

逻辑说明 #

1. CTE分组排序：用ROW_NUMBER()按Booking_number分区，按Sales_Order排序，给每个Booking_number下的记录分配行号，行号1即为根节点记录。
2. 根节点筛选：查询行号为1的记录作为根节点。
3. Sub_Rows生成：子查询筛选同一Booking_number下行号大于1的记录，生成嵌套的JSON数组；若无符合条件的记录，Sub_Rows字段会自动省略，与预期格式一致。

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