I am using Microsoft SQL server, and I have a variable in SQL that contains a JSON array. Now, I am trying to update one property and set it to 0 for all the JSON objects inside the JSON array.

I have tried the following query, but it just updates the first JSON object.

DECLARE @Json varchar(MAX), @updatedJson varchar(MAX);
SET @Json ='[{"LocationID":1234,"LocationName":"ABCD","MTML":1},{"LocationID":12345,"LocationName":"LMNO","MTML":3}]'
SET @updatedJson = JSON_MODIFY(@Json, '$[0].MTML', 0);
SELECT @updatedJson;

I know that I can add one more statement like:

SET @updatedJson = JSON_MODIFY(@updatedJson, '$[1].MTML', 0);

to the above query and update the second JSON object. But I would like to see some suggestions to do this in a generic way and not for specific array elements.

Would highly appreciate any help!