如何使用SQL Server脚本向JSON现有数组插入新元素

• JSON_MODIFY的append关键字作用是向数组末尾新增独立顶层元素，不会修改数组内已有子对象的属性，原写法只会把传入值当成新条目追加到Fruits数组最后，不会给现有水果对象加字段
• 定义的@appendnewjson值Column4:Sweet不符合标准JSON格式，SQL Server的JSON函数处理属性值时要求键名、字符串值都用双引号包裹，键值对需放在合法JSON结构中
• 没有遍历Fruits数组的每个子元素，无法实现给所有元素统一加属性的需求

以下T-SQL代码可在SQL Server 2016及以上版本运行，实现给Fruits数组所有元素添加"Column4": "Sweet"属性的需求：

DECLARE @json NVARCHAR(MAX);
SET @json = '{"Heading":[{"Index":1,"Name":"Empty"},{"Index":2,"Name":"TypeOfFruit"},{"Index":3,"Name":"Color"},{"Index":3,"Name":"Taste"}],"Fruits":[{"Column1":"","Column2":"Mango","Column3":"Yellow"},{"Column1":"","Column2":"Coconut","Column3":"Green"},{"Column1":"","Column2":"Banana","Column3":"Yellow"},{"Column1":"","Column2":"Apple","Column3":"Red"},{"Column1":"","Column2":"Watermelon","Column3":"Green"}]}';

-- 拆解Fruits数组为行集，补充Column4字段后重新生成JSON数组
WITH UpdatedFruits AS (
    SELECT
        JSON_VALUE(item.value, '$.Column1') AS Column1,
        JSON_VALUE(item.value, '$.Column2') AS Column2,
        JSON_VALUE(item.value, '$.Column3') AS Column3,
        'Sweet' AS Column4
    FROM OPENJSON(@json, '$.Fruits') AS item
)
SET @json = JSON_MODIFY(
    @json,
    '$.Fruits',
    JSON_QUERY((SELECT * FROM UpdatedFruits FOR JSON PATH))
);

-- 输出更新后的结果
SELECT @json AS UpdatedJson;

• 用OPENJSON函数将$.Fruits路径下的JSON数组拆解为多行数据，读取每个水果对象原有的三个字段值
• 为拆解后的每一行固定添加Column4字段，值统一设为Sweet
• 用FOR JSON PATH将加完字段的行集重新转换为标准JSON数组
• 用JSON_MODIFY替换原JSON中的Fruits节点，外层包裹JSON_QUERY是为了告知SQL Server传入的是合法JSON片段，避免内容被转义为普通字符串

补充说明：如果需求是往Fruits数组末尾新增一个完整的水果对象，才需要使用append修饰符，修改数组内已有元素属性时不适用该语法。

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