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

Question

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": [
        {
            "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"
        }
    ]
}

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": [
        {
            "Column1": "",
            "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":[{"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"}]}';
SET @appendnewjson='Column4:Sweet'
SELECT @json AS 'Before', 
    JSON_MODIFY(@json, 'append $.Fruits', @appendnewjson) AS 'After';

Thank you in advance

While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Jun 12, 2022 at 2:52

marc_s · Accepted Answer · 2022-06-19 20:51:37Z

1

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":[{"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"}]}';
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": [{
        "Column1": "",
        "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.

edited Jun 19, 2022 at 20:51

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jun 11, 2022 at 2:50

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Reyes Over a year ago

Thank you for this answer, but unfortunately I can only use SQL Server 2016, in which I believe that the JSON_PATH can only accept string literal. That's why I'm getting an error. "Msg 13610, Level 16, State 2, Line 5 The argument 2 of the "JSON_MODIFY" must be a string literal."

marc_s · Accepted Answer · 2022-06-19 20:51:24Z

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":[{"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"}]}';

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

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related