SQL JSON AUTO生成JSON结构调整：Items与Locations数组同级

核心思路 #

FOR JSON AUTO会根据表的JOIN顺序自动生成嵌套结构，你当前的JOIN顺序是header → locations → items，所以items会被嵌套在locations内部。要实现locations和items同级，需要改用**FOR JSON PATH手动定义JSON层级**，通过子查询分别生成两个独立的数组，直接作为header对象的同级属性。

修改后的SQL语句 #

SELECT
    -- Header 基础字段
    header.load_id                  AS shipmentNumber,
    header.load_id                  AS tripNumber, 
    'TMS'                           AS tmsID,
    CASE 
        WHEN header.status = '4' THEN 'CANCELED'  
        WHEN header.status IN ('2','0') THEN 'UPDATE'
        WHEN header.status = '3' THEN 'SHIPPED'
        WHEN header.status = '1' THEN 'CREATED'  
        ELSE 'UNKNOWN'
    END AS purpose,
    header.insert_datetime          AS erpCreatedDateTime,
    'Outbound'                      AS movementType, 
    'PREPAID'                       AS freightTerms,
    header.status                   AS orderStatus, 
    'OBDRY'                         AS divisionCode, 
    header.total_pallets            AS palletPositions,
    -- 生成独立的locations数组
    (
        SELECT
            locations.go_destination        AS locationID,
            locations.stop_sequence         AS stopSequenceNumber,
            locations.stoprole              AS stopRole,
            locations.go_name               AS name,
            locations.go_addr1              AS address1,
            locations.go_addr2              AS address2,
            locations.go_city               AS city, 
            locations.go_provcode           AS stateProvidenceCode,
            locations.go_postalcode         AS postalCode,
            locations.go_countrycode        AS countryCode, 
            locations.go_contact            AS contact,
            locations.go_phone              AS phone,
            locations.go_email              AS email,
            locations.estimated_datetime    AS estimatedDateTime,
            locations.scheduled_datetime    AS scheduledDateTime,
            'NA'                            AS truckArriveDateTime,
            locations.truckDepart_datetime  AS truckDepartDateTime
        FROM t_export_outbound_destination locations
        WHERE header.import_id = locations.import_id
          AND header.wh_id = locations.wh_id
        FOR JSON PATH
    ) AS locations,
    -- 生成独立的items数组
    (
        SELECT
            items.order_number              AS orderNumber,
            'Cases'                         AS packagingType,
            items.netWeight                 AS netWeight,
            items.volume                    AS volume,
            items.case_qty                  AS packageQuantity
        FROM t_export_outbound_order items
        WHERE header.import_id = items.import_id
          AND header.wh_id = items.wh_id
        FOR JSON PATH
    ) AS items
FROM t_export_outbound_load header
WHERE header.import_id = @import_id
FOR JSON PATH, ROOT('record')

关键说明 #

1. 子查询生成独立数组：通过两个子查询分别查询locations和items的数据，并用FOR JSON PATH生成数组，直接赋值给外层查询的locations和items字段，确保两者成为header对象的同级属性。
2. 处理空数组场景：如果某个header没有关联的items，子查询会返回NULL。若要强制生成空数组[]，可以用ISNULL()包裹子查询，示例：

   ISNULL(
       (SELECT ... FOR JSON PATH),
       '[]'
   ) AS items
   

3. 简化CASE表达式：原语句中status='2'和status='0'都返回UPDATE，用IN合并逻辑更简洁。

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