json_modify in SQL Server 2019 script is not updating table when using cross apply with multiple elements

Question

I have an SQL Server 2019 table with the following data:

CREATE TABLE mytable2 
(
    jsondata nvarchar(max)
)

INSERT INTO mytable2
VALUES ('
{
    "name": "Daily06012023",
    "documents": [
    {
            "imageUrl": "s3://2023/129/2312908000.tif",
            "json": {
                "indexingRecordId": "2312908000",
                "instrumentNumber": "2312908000"
                              
        }
        },
        {
            "imageUrl": "s3://2023/129/2312908000.tif",
            "json": {
                "indexingRecordId": "2312908001",
                "instrumentNumber": "2312908001"
                              
        }
        },
        {
            "imageUrl": "s3://2023/129/2312908000.tif",
            "json": {
                "indexingRecordId": "2312908002",
                "instrumentNumber": "2312908002"
                              
        }
        },
        {
            "imageUrl": "s3://2023/129/2312908000.tif",
            "json": {
                "indexingRecordId": "2312908003",
                "instrumentNumber": "2312908003"
                              
        }
        }
    ]   

}      
'      
)

I'm trying to use json_modify to update the imageUrl for a specific indexingRecordId. However, none of my scripts actually update the data in the json. Some examples I see want me to use a [0] document position, but I'm running these updates quite often in a json with hundreds or thousands of records so I won't always know the document position.

Does anyone have a suggestion to modify this script to apply my update correctly?

I've tried this script:

UPDATE mytable2
SET jsondata = JSON_MODIFY(jsondata, '$.documents.imageUrl', 's3://NEW_Path/2312908003.tif')
FROM mytable2
CROSS APPLY OPENJSON(jsondata, '$.documents') AS documents
WHERE JSON_VALUE(documents.value, '$.json.indexingRecordId')  = '2312908003'

It says one record updated, but the data didn't actually get updated.

Does anyone have a suggestion to modify this script to update my data better?

Answer 1

You can either rebuild the whole $.documents property

UPDATE t2
SET jsondata = JSON_MODIFY(jsondata, '$.documents', j.documents)
FROM mytable2 t2
CROSS APPLY (
    SELECT
      IIF(
        indexingRecordId = '2312908003',
        's3://NEW_Path/2312908003.tif',
        imageUrl
      ) AS imageUrl,
      indexingRecordId AS [json.indexingRecordId],
      instrumentNumber AS [json.instrumentNumber]
    FROM OPENJSON(jsondata, '$.documents')
      WITH (
        imageUrl nvarchar(1000),
        indexingRecordId nvarchar(1000) '$.json.indexingRecordId',
        instrumentNumber nvarchar(1000) '$.json.instrumentNumber'
      ) AS documents
    FOR JSON PATH
) j(documents);

db<>fiddle

Or, if you only have one value to update (per whole mytable2 row), you can grab its path and make that one update:

UPDATE t2
SET jsondata = JSON_MODIFY(jsondata, N'$.documents[' + j.idx + N'].imageUrl', 's3://NEW_Path/2312908003.tif')
FROM mytable2 t2
CROSS APPLY (
    SELECT TOP (1) j1.[key] AS idx
    FROM OPENJSON(jsondata, '$.documents') j1
    CROSS APPLY OPENJSON(j1.value)
      WITH (
        indexingRecordId nvarchar(1000) '$.json.indexingRecordId'
      ) AS documents
    WHERE indexingRecordId = '2312908003'
) j
WHERE j.idx IS NOT NULL;

db<>fiddle