SQL Server动态从另一表追加至数组指定索引及JSON字符串修改需求

没问题，我来帮你搞定这两个需求，分两部分详细说明：

要实现这个功能，核心思路是先解析原JSON数组，将其拆分为「目标索引前的元素」「待插入的表数据」「目标索引后的元素」三部分，再重新拼接成新数组替换原JSON的对应节点。以下是具体实现步骤和示例代码：

前提假设 #

• 存储原JSON的表：SourceTable，包含列JsonData（即你提供的目标JSON）
• 待追加的数据表：AdditionalData，结构与value数组的元素完全一致（字段：AccountProductListId, Name, AccountId, IsPublic, Comment, Quantity）
• 目标插入索引：@TargetIndex（比如设为1，代表在数组第2个元素前插入）

示例代码 #

DECLARE @TargetIndex INT = 1;
-- 获取原JSON数据（根据实际条件过滤）
DECLARE @OriginalJson NVARCHAR(MAX) = (SELECT JsonData FROM SourceTable WHERE Id = 1);

-- 1. 解析原JSON数组，保留元素索引
WITH OriginalElements AS (
    SELECT 
        CAST([key] AS INT) AS ElementIndex,
        value AS ElementJson
    FROM OPENJSON(@OriginalJson, '$.value')
),
-- 2. 将待插入的表数据转换为JSON格式的单个元素
NewElements AS (
    SELECT (
        SELECT AccountProductListId, Name, AccountId, IsPublic, Comment, Quantity 
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS ElementJson
    FROM AdditionalData
),
-- 3. 按顺序拼接所有元素：前半段 + 新元素 + 后半段
CombinedElements AS (
    SELECT ElementJson, ElementIndex FROM OriginalElements WHERE ElementIndex < @TargetIndex
    UNION ALL
    SELECT ElementJson, @TargetIndex AS ElementIndex FROM NewElements -- 给新元素指定索引排序
    UNION ALL
    SELECT ElementJson, ElementIndex FROM OriginalElements WHERE ElementIndex >= @TargetIndex
)
-- 4. 生成新数组并替换原JSON的value节点，同时更新RecordCount
SELECT JSON_MODIFY(
    JSON_MODIFY(@OriginalJson, '$.value', 
        (SELECT STRING_AGG(ElementJson, ',') WITHIN GROUP (ORDER BY ElementIndex) 
         FROM CombinedElements FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
    ),
    '$.RecordCount', 
    (SELECT COUNT(*) FROM OriginalElements) + (SELECT COUNT(*) FROM NewElements)
) AS UpdatedJson;

注意事项 #

• SQL Server 2017及以上版本支持STRING_AGG函数，如果是旧版本，需要用FOR XML PATH的方式拼接字符串替代。
• 若待插入的数据有多条，NewElements会自动生成多个JSON元素，全部插入到指定位置。

你提供的原JSON最后一个元素存在不完整的"..."，我先补全并给出修改示例，同时提供SQL层面的修改方法：

手动修改后的完整JSON示例（含常见修改） #

比如我们做以下修改：

1. 将RecordCount更新为4（对应新增一个元素）
2. 在索引1的位置插入一条新元素
3. 补全最后一个元素的Comment和Quantity字段

{
    "RecordCount": 4,
    "Top": 10,
    "Skip": 0,
    "SelectedSort": "Seed asc",
    "value": [
        {
            "AccountProductListId": 22091612871138,
            "Name": "April 4th 2018",
            "AccountId": 256813438078643,
            "IsPublic": false,
            "Comment": "Test order sheet",
            "Quantity": 3
        },
        {
            "AccountProductListId": 12345678901234,
            "Name": "Dynamically Added List",
            "AccountId": 256813438078643,
            "IsPublic": false,
            "Comment": "From AdditionalData table",
            "Quantity": 2
        },
        {
            "AccountProductListId": 166305848801939,
            "Name": "test",
            "AccountId": 256813438078643,
            "IsPublic": false,
            "Comment": "",
            "Quantity": 1
        },
        {
            "AccountProductListId": 21177711287586,
            "Name": "Test Order sheet",
            "AccountId": 256813438078643,
            "IsPublic": true,
            "Comment": "Public shared list",
            "Quantity": 5
        }
    ]
}

SQL层面修改原JSON的示例 #

如果原JSON存储在变量或表中，用JSON_MODIFY函数可以精准修改指定节点：

DECLARE @OriginalJson NVARCHAR(MAX) = '{"RecordCount":3,"Top":10,"Skip":0,"SelectedSort":"Seed asc","value":[{"AccountProductListId":22091612871138,"Name":"April 4th 2018","AccountId":256813438078643,"IsPublic":false,"Comment":"Test order sheet","Quantity":3},{"AccountProductListId":166305848801939,"Name":"test","AccountId":256813438078643,"IsPublic":false,"Comment":"","Quantity":1},{"AccountProductListId":21177711287586,"Name":"Test Order sheet","AccountId":256813438078643,"IsPublic":true,"Comment":"","Quantity":0}]}';

-- 1. 修改最后一个元素的Comment和Quantity
SET @OriginalJson = JSON_MODIFY(
    JSON_MODIFY(@OriginalJson, '$.value[2].Comment', 'Public shared list'),
    '$.value[2].Quantity', 5
);

-- 2. 更新RecordCount为4
SET @OriginalJson = JSON_MODIFY(@OriginalJson, '$.RecordCount', 4);

SELECT @OriginalJson AS UpdatedJson;

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