EF迁移中优化JSON列数组新增字段的SQL写法（弃用STRING_AGG）

我正在编写EF迁移来更新JSON列数据，需要将联系人的FirstName和LastName合并为单个Name字段。目前我写出了依赖STRING_AGG做字符串拼接的代码，但想找一种无需手动拼接JSON数组的更优写法——之前尝试用FOR JSON PATH时总会生成额外的节点。

现有可用代码（能生成正确输出，但依赖字符串拼接）：

DECLARE @t AS TABLE 
              (
                  id int IDENTITY(1, 1), 
                  details nvarchar(max)
              )

INSERT INTO @t (details) 
VALUES (
 N'{
    "Contacts": 
    [
        {"Id": 1, "FirstName": "John", "LastName": "Doe"}, 
        {"Id": 2, "FirstName": "Peter", "LastName": "Pan"}
    ]
}')

SELECT 
    -- will replace with UPDATE
    JSON_MODIFY(
        details,
        '$.Contacts',
        JSON_QUERY((
            SELECT '[' + STRING_AGG(updated_json, ',') + ']'
            FROM (
                SELECT JSON_MODIFY(
                    c.[value], 
                    '$.Name', 
                    CONCAT(ISNULL(NULLIF(JSON_VALUE(value, '$.LastName'), ''), ''), ', ', ISNULL(NULLIF(JSON_VALUE(value, '$.FirstName'), ''), ''))
                ) as updated_json
                FROM OPENJSON(Details, '$.Contacts') c
            ) t
        ))
    )
FROM @t

正确输出：

{   
    "Contacts":
    [
        {"Id": 1, "FirstName": "John", "LastName": "Doe", "Name": "Doe, John"},
        {"Id": 2, "FirstName": "Peter", "LastName": "Pan", "Name": "Pan, Peter"}
    ]
}

之前失败的尝试（会生成updated_json额外节点）：

SELECT 
    -- this code returns the "updated_json" extra node
    JSON_MODIFY(
        details,
        '$.Contacts',
        JSON_QUERY(
            (SELECT 
                 JSON_MODIFY(value, '$.FirstName',
                             CONCAT(ISNULL(NULLIF(JSON_VALUE(value, '$.LastName'), ''), ''), ', ', ISNULL(NULLIF(JSON_VALUE(value, '$.FirstName'), ''), '')) 
                    ) as updated_json
             FROM OPENJSON(Details, '$.Contacts')
             FOR JSON PATH, WITHOUT_ARRAY_WRAPPER))
    )
FROM @t

优化方案 #

你之前的尝试生成额外节点，核心原因是为JSON_MODIFY的结果指定了updated_json别名，导致FOR JSON PATH会将每个对象包裹在该键下。只需去掉列别名，直接返回修改后的JSON对象，即可避免这个问题，同时无需手动拼接数组字符串。

优化后的代码：

DECLARE @t AS TABLE 
(
    id int IDENTITY(1, 1), 
    details nvarchar(max)
)

INSERT INTO @t (details) 
VALUES (
N'{
    "Contacts": 
    [
        {"Id": 1, "FirstName": "John", "LastName": "Doe"}, 
        {"Id": 2, "FirstName": "Peter", "LastName": "Pan"}
    ]
}')

SELECT 
    JSON_MODIFY(
        details,
        '$.Contacts',
        JSON_QUERY(
            (
                SELECT 
                    JSON_MODIFY(
                        c.[value], 
                        '$.Name', 
                        CONCAT(
                            ISNULL(NULLIF(JSON_VALUE(c.[value], '$.LastName'), ''), ''), 
                            ', ', 
                            ISNULL(NULLIF(JSON_VALUE(c.[value], '$.FirstName'), ''), '')
                        )
                    )
                FROM OPENJSON(details, '$.Contacts') c
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            )
        )
    ) AS UpdatedDetails
FROM @t

关键说明 #

1. 移除列别名：不再给JSON_MODIFY的结果指定updated_json别名，FOR JSON PATH会直接将每个修改后的对象作为数组元素输出，不会添加额外节点。
2. 保留JSON_QUERY包裹：确保JSON_MODIFY识别生成的内容为JSON对象，而不是将其转义为字符串类型。
3. 逻辑复用：保留了你原有的姓名合并逻辑（处理空值的ISNULL(NULLIF(...))），确保边界情况的兼容性。

执行后输出与你预期的正确结果完全一致，且全程无需手动拼接JSON数组的字符串操作。

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