Update JSON using JSON_MODIFY

Example:

Your JSON is an array of JSON objects, so you need an index to access each element. In this case, one possible approach is to use an expression as path parameter for JSON_MODIFY. Note, that this feature is available in SQL Server 2017 (14.x) and in Azure SQL Database. The JSON array is splitted into elements using OPENJSON with default schema and in this case the returned columns are key, value and type.

Basic example:

Statement:

DECLARE @json nvarchar(max) = N'{
    "data": [{
            "identifier": 1,
            "someData": {
                "sample1": "lorem 1",
                "sample2": "test 1"
            }
        },
        {
            "identifier": 2,
            "someData": {
                "sample1": "lorem 2",
                "sample2": "test 2"
            }
        },
        {
            "identifier": 3,
            "someData": {
                "sample1": "lorem 3",
                "sample2": "test 3"
            }
        }
    ]
}'

SELECT JSON_MODIFY(@json, '$.data[' + j.[key] + '].someData.sample1', N'NewValue') AS JsonData
FROM OPENJSON(@json, '$.data') j
WHERE JSON_VALUE([value], '$.identifier') = 3

Output:

----------------------------
JsonData
----------------------------
{
    "data": [{
            "identifier": 1,
            "someData": {
                "sample1": "lorem 1",
                "sample2": "test 1"
            }
        },
        {
            "identifier": 2,
            "someData": {
                "sample1": "lorem 2",
                "sample2": "test 2"
            }
        },
        {
            "identifier": 3,
            "someData": {
                "sample1": "NewValue",
                "sample2": "test 3"
            }
        }
    ]
}

Table example:

Table:

CREATE TABLE #Data (
  ID int,
  Data nvarchar(max)
)
INSERT INTO #Data
   (ID, Data)
VALUES
   (1, N'{
    "data": [{
            "identifier": 1,
            "someData": {
                "sample1": "lorem 1",
                "sample2": "test 1"
            }
        },
        {
            "identifier": 2,
            "someData": {
                "sample1": "lorem 2",
                "sample2": "test 2"
            }
        },
        {
            "identifier": 3,
            "someData": {
                "sample1": "lorem 3",
                "sample2": "test 3"
            }
        }
    ]
}'),
   (2, N'{
    "data": [{
            "identifier": 1,
            "someData": {
                "sample1": "lorem 1",
                "sample2": "test 1"
            }
        },
        {
            "identifier": 2,
            "someData": {
                "sample1": "lorem 2",
                "sample2": "test 2"
            }
        },
        {
            "identifier": 3,
            "someData": {
                "sample1": "lorem 3",
                "sample2": "test 3"
            }
        }
    ]
}')

Statement:

SELECT 
   d.ID, c.Data
FROM #Data d
CROSS APPLY (
   SELECT JSON_MODIFY(d.Data, N'$.data[' + CONVERT(nvarchar(max), j.[key] COLLATE Latin1_General_CI_AS) + N'].someData.sample1', N'NewValue') AS Data
   FROM OPENJSON(d.Data, '$.data') j
   WHERE JSON_VALUE([value], '$.identifier') = 3
) c

Notes:

The key column has a BIN2 collation, so you need to convert with collate option.