Insert a new element in an existing array in JSON using SQL Server script-CodePudding

How can I append an element in an existing array in JSON using SQL? I want to update the Fruits object and want to insert a new element in the existing array.

Here's the JSON I want to Update.

    {
    "Heading": [
        {
            "Index": 1,
            "Name": "Empty"
        },
        {
            "Index": 2,
            "Name": "TypeOfFruit"
        },
        {
            "Index": 3,
            "Name": "Color"
        },
        {
            "Index": 3,
            "Name": "Taste"
        }
    ],
    "Fruits": [
        {
            "Columm1": "",
            "Column2": "Mango",
            "Column3": "Yellow"
        },
        {
            "Column1": "",
            "Column2": "Coconut",
            "Column3": "Green"
        },
        {
            "Column1": "",
            "Column2": "Banana",
            "Column3": "Yellow"
        },
        {
            "Column1": "",
            "Column2": "Apple",
            "Column3": "Red"
        },
        {
            "Column1": "",
            "Column2": "Watermelon",
            "Column3": "Green"
        }
    ]
}

Here's the after that I want after the update. I want to insert the element "Column4": "Sweet" in the Fruits object existing arrays.

    {
    "Heading": [
        {
            "Index": 1,
            "Name": "Empty"
        },
        {
            "Index": 2,
            "Name": "TypeOfFruit"
        },
        {
            "Index": 3,
            "Name": "Color"
        },
        {
            "Index": 3,
            "Name": "Taste"
        }
    ],
    "Fruits": [
        {
            "Columm1": "",
            "Column2": "Mango",
            "Column3": "Yellow"
            "Column4": "Sweet"
        },
        {
            "Column1": "",
            "Column2": "Coconut",
            "Column3": "Green"
            "Column4": "Sweet"
        },
        {
            "Column1": "",
            "Column2": "Banana",
            "Column3": "Yellow"
            "Column4": "Sweet"
        },
        {
            "Column1": "",
            "Column2": "Apple",
            "Column3": "Red"
            "Column4": "Sweet"
        },
        {
            "Column1": "",
            "Column2": "Watermelon",
            "Column3": "Green"
            "Column4": "Sweet"
        },
        
    ]
}

Here is the code that I'm using and want to know if there's something I'm missing here.

DECLARE @json NVARCHAR(MAX), @appendnewjson VARCHAR(MAX);
SET @json = '{"Heading":[{"Index":1,"Name":"Empty"},{"Index":2,"Name":"TypeOfFruit"},{"Index":3,"Name":"Color"},{"Index":3,"Name":"Taste"}],"Fruits":[{"Columm1":"","Column2":"Mango","Column3":"Yellow"},{"Column1":"","Column2":"Coconut","Column3":"Green"},{"Column1":"","Column2":"Banana","Column3":"Yellow"},{"Column1":"","Column2":"Apple","Column3":"Red"},{"Column1":"","Column2":"Watermelon","Column3":"Green"}]}';
SET @appendnewjson='Column4:Sweet'
SELECT @json AS 'Before', 
    JSON_MODIFY(@json, 'append $.Fruits', @appendnewjson) AS 'After';

Thank you in advance

CodePudding user response：

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

SELECT @json = JSON_MODIFY(@json, CONCAT('$.Fruits[', [key], '].Column4'), 'sweet')
FROM OPENJSON(@json, '$.Fruits');
SELECT @json;

Would give you:

(Kein Spaltenname)
{
    "Heading": [{
        "Index": 1,
        "Name": "Empty"
    }, {
        "Index": 2,
        "Name": "TypeOfFruit"
    }, {
        "Index": 3,
        "Name": "Color"
    }, {
        "Index": 3,
        "Name": "Taste"
    }],
    "Fruits": [{
        "Columm1": "",
        "Column2": "Mango",
        "Column3": "Yellow",
        "Column4": "sweet"
    }, {
        "Column1": "",
        "Column2": "Coconut",
        "Column3": "Green",
        "Column4": "sweet"
    }, {
        "Column1": "",
        "Column2": "Banana",
        "Column3": "Yellow",
        "Column4": "sweet"
    }, {
        "Column1": "",
        "Column2": "Apple",
        "Column3": "Red",
        "Column4": "sweet"
    }, {
        "Column1": "",
        "Column2": "Watermelon",
        "Column3": "Green",
        "Column4": "sweet"
    }]
}

But you need at least SQL Server 2017 for this.

CodePudding user response：

You can do this by breaking open the Fruits property into separate rows, modifying each one, then re-aggregating them, and finally storing them back

DECLARE @json NVARCHAR(MAX);

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

SELECT @json AS 'Before', 
    JSON_MODIFY(@json, '$.Fruits', JSON_QUERY((
        SELECT '['   STRING_AGG(JSON_MODIFY(j.value, '$.Column4', 'Sweet'), ',')   ']'
        FROM OPENJSON(@json, '$.Fruits') j
    ))) AS 'After';

SQL Fiddle