如何生成含唯一顶级Identifier字段的多行JSON数组

需求与问题 #

现有Items和ContentItems表（一对一关联），需要生成如下结构的JSON：

• 顶级包含唯一不重复的Identifier字段
• 同级包含Items数组，数组元素为收件人及对应内容信息

期望输出 #

{
    "Identifier": "71A08718-4D35-4661-BCB9-DA8BA13F005E",
    "Items": [{
        "RecipientName": "Name1",
        "RecipientSurname": "Surname1",
        "RecipientContactNumber": "10001000",
        "RecipientEmail": "email@email.com",
        "Contents": {
            "ItemDescription": "description 1",
            "ItemQuanity": 1,
            "ItemNetWeightg": 10
        }
    }, {
        "RecipientName": "Name2",
        "RecipientSurname": "Surname2",
        "RecipientContactNumber": "20002000",
        "RecipientEmail": "email2email2.com",
        "Contents": {
            "ItemDescription": "description 1",
            "ItemQuanity": 1,
            "ItemNetWeightg": 10
        }
    }]
}

当前错误输出 #

原代码生成的JSON中，Identifier会重复出现在每个Items元素内，且结构不符合要求：

{
    "Items": [{
            "Identifier": "71A08718-4D35-4661-BCB9-DA8BA13F005E",
            "RecipientName": "Name1",
            "RecipientSurname": "Surname1",
            "RecipientContactNumber": "10001000",
            "RecipientEmail": "email@email.com",
            "Contents": {
                "ItemDescription": "description 1",
                "ItemQuanity": 1,
                "ItemNetWeightg": 10
            }
        }, {
            "Identifier": "329CC547-D418-4863-A50C-DFB072B66FE7",
            "RecipientName": "Name2",
            "RecipientSurname": "Surname2",
            "RecipientContactNumber": "20002000",
            "RecipientEmail": "email2email2.com",
            "Contents": {
                "ItemDescription": "description 1",
                "ItemQuanity": 1,
                "ItemNetWeightg": 10
            }
        }
    ]
}

修正方案 #

原代码的问题在于：

1. NEWID()放在子查询中，每一行都会生成新的GUID，导致每个Items元素都有独立的Identifier
2. 使用ROOT('Items')将所有结果包裹在Items数组内，无法生成顶级的Identifier字段

修正思路是：先单独生成Items数组的JSON内容，再在外层查询中生成唯一的Identifier并与Items数组组合成目标结构。

修正后的完整代码 #

DECLARE @Items TABLE (
    ID bigint
    ,Number nvarchar(20)
    ,Date datetime2
    ,OASNumber nvarchar(20)
    ,ContactName nvarchar(50)
    ,ContactSurname nvarchar(50)
    ,Mobile nvarchar(20)
    ,Email nvarchar(50)
    ,Type nvarchar(50)
)

DECLARE @ContentItems TABLE 
(
    Id bigint
    ,OrderNumber nvarchar(50)
    ,ItemDescription nvarchar(max)
    ,Quantity int
)

INSERT INTO @Items
SELECT 1,'ON1',GETDATE(),'OAS1','Name1','Surname1','10001000','email@email.com','Sales of Goods'
INSERT INTO @ContentItems
SELECT 1,'ON1','description 1',1

INSERT INTO @Items
SELECT 2,'ON2',GETDATE(),'OAS2','Name2','Surname2','20002000','email2email2.com','Sales of Goods'
INSERT INTO @ContentItems
SELECT 3,'ON2','description 1',1

-- 先生成Items数组的JSON内容
DECLARE @ItemsJson nvarchar(max)
SET @ItemsJson = (
    SELECT 
        D.ContactName AS [RecipientName]
        ,D.ContactSurname AS [RecipientSurname]
        ,D.Mobile AS [RecipientContactNumber]
        ,D.Email AS [RecipientEmail]
        ,Item.ItemDescription AS [Contents.ItemDescription]
        ,Item.Quantity AS [Contents.ItemQuanity]
        ,10 AS [Contents.ItemNetWeightg]
    FROM @Items D
    JOIN @ContentItems Item ON Item.OrderNumber = D.Number
    FOR JSON PATH
)

-- 外层组合Identifier和Items数组，生成最终JSON
SELECT 
    NEWID() AS Identifier,
    JSON_QUERY(@ItemsJson) AS Items
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

代码说明 #

• @ItemsJson变量存储单独生成的Items数组JSON，避免将Identifier混入每个元素
• JSON_QUERY()用于保留@ItemsJson的JSON结构，防止被自动转义为字符串
• WITHOUT_ARRAY_WRAPPER去掉外层默认的数组包装，得到单一的顶级JSON对象
• NEWID()仅在顶层执行一次，生成唯一的Identifier

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