Not able to update one of the key value by using JSON_MODIFY function in SQL Server I have the following json record and I'm trying to update the 'counter2' value to 2000 using JSON_MODIFY function. The query doesn't throw any exception but at the same time the value is not getting updated. 

Table Data:



Json data: 

[ {"counter1": 9,
"counter2": 1000,
"counter3": 109 }]


SQL:

UPDATE test01
set json_data = JSON_MODIFY(json_data,'$.counter2',2000)
where test01_id =10


Am I missing something here?
Your 'counter' properties are in an object within an array, so your JSON path in JSON_MODIFY isn't correct. You need to reference the index of the item in the array to correctly update the value:

UPDATE test01
set json_data = JSON_MODIFY(json_data,'$[0].counter2',2000)
where test01_id =10


If you had additional objects in the array with different counter 1/2/3 values, then you would need to reference them with their correct index, i.e. [1], [2] and so on. You can see a working example of this here.