如何用FOR JSON PATH生成含数组结构的API所需JSON？

需要将SQL查询结果转换为API所需的JSON格式，其中fulfillments和shipments必须以数组形式存在（支持多行数据）。目标JSON格式如下：

{
    "customer_order": {
        "order_number": "12394334"
    },
    "fulfillments": [
        {
            "fulfillment_id": "12394334"
        }
    ],
    "destination": {
        "contact": {
            "person_name": "Johnny Pops",
            "phone_number": 777777777,
            "email_address": "JohnnyPops@gmaill.com"
        },
        "address": {
            "street_line1": "1708 Johnny Pops DRIVE",
            "city": "AUSTIN",
            "postal_code": 78745,
            "state_or_province_code": "TX",
            "country_code": "US"
        }
    },
    "shipments": [
        {
            "shipment_id": "BLDT11121",
            "fulfillment_id": "BLDT11121",
            "tracking_number": "BLDT11121",
            "carrier_scac": "SSSS"
        }
   ]
}

当前使用FOR JSON PATH, WITHOUT_ARRAY_WRAPPER生成JSON，但无法为fulfillments和shipments添加正确的数组括号。现有SQL语句：

SELECT 
    [Order Number] AS [customer_order.order_number], [Order Number] AS [fulfillments.fulfillment_id]
    ,[Last Name] AS [destination.contact.person_name], [Home Phone] AS [destination.contact.phone_number], email AS [destination.contact.email_address]
    ,[Address] AS [destination.address.street_line1], [city] AS [destination.address.city], zip AS [destination.address.postal_code]
    ,[State] AS [destination.address.state_or_province_code], 'US' AS [destination.address.country_code]
    ,[Order Idwaybill] AS [shipments.shipment_id], [Order Idwaybill] AS [shipments.fulfillment_id], [Order Idwaybill] AS [shipments.tracking_number]
    ,'SEKW' AS [shipments.carrier_scac]
FROM vw__orders
WHERE [Order Number] = '12394334'

目前可以通过REPLACE()函数手动替换字符串实现需求，例如：

REPLACE(SQL, '"fulfillments":{"', '"fulfillments":[{"')

但希望找到无需替换操作的规范语法实现该需求。

可以通过嵌套子查询+FOR JSON PATH的方式，让SQL Server自动生成数组结构，完全不需要字符串替换。核心思路是将fulfillments和shipments的内容作为独立子查询，子查询使用FOR JSON PATH时会自动生成数组，外层查询再将这些数组字段整合到最终JSON中。

修改后的SQL语句 #

SELECT
    -- 主订单信息（单个对象）
    (SELECT [Order Number] AS order_number 
     FROM vw__orders 
     WHERE [Order Number] = '12394334' 
     FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS customer_order,
    -- 生成fulfillments数组
    (SELECT [Order Number] AS fulfillment_id 
     FROM vw__orders 
     WHERE [Order Number] = '12394334' 
     FOR JSON PATH) AS fulfillments,
    -- 合并生成destination对象
    (SELECT 
        (SELECT [Last Name] AS person_name, 
                [Home Phone] AS phone_number, 
                email AS email_address 
         FROM vw__orders 
         WHERE [Order Number] = '12394334' 
         FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS contact,
        (SELECT [Address] AS street_line1, 
                city, 
                zip AS postal_code, 
                [State] AS state_or_province_code, 
                'US' AS country_code 
         FROM vw__orders 
         WHERE [Order Number] = '12394334' 
         FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS address
     FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS destination,
    -- 生成shipments数组
    (SELECT [Order Idwaybill] AS shipment_id, 
            [Order Idwaybill] AS fulfillment_id, 
            [Order Idwaybill] AS tracking_number, 
            'SEKW' AS carrier_scac 
     FROM vw__orders 
     WHERE [Order Number] = '12394334' 
     FOR JSON PATH) AS shipments
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

关键逻辑说明 #

• 对于需要数组结构的fulfillments和shipments，子查询仅使用FOR JSON PATH（不添加WITHOUT_ARRAY_WRAPPER），SQL Server会自动为结果添加数组括号[]，天然支持多行数据场景。
• 对于单个对象（如customer_order、destination下的子节点），子查询添加WITHOUT_ARRAY_WRAPPER，避免生成多余的数组结构。
• 这种方式完全遵循SQL Server的JSON生成规范，不会出现字符串替换可能导致的格式错误，扩展性更强。

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