A bug in JSON_MODIFY?

Question

Hi everyone

I think this is a known bug, right? Note that json gets corrupted, the second and third array elements get mingled up.

DECLARE @j nvarchar(max) =  
'{  
  "version": "1.0",  
  "content": [  
    {},  
    {  
      "colSpan": 2,  
      "visibleIndex": 1,  
      "dataField": "Path",  
      "name": "Path",  
      "editorType": "dxTextBox",  
      "editorOptions": {  
        "placeholder": "Directory"  
      },  
      "groupItemOptions": {  
        "name": "output",  
        "colCount": 2  
      }  
    },  
        {  
            "dataField": "Queue",  
            "name": "Queue",          
            "editorType": "dxTextBox",  
            "visibleIndex": 2,  
            "colSpan": 2,  
            "editorOptions": {  
                "placeholder": "MSMQ Queue"  
            },  
            "groupItemOptions": {  
                "name": "output"  
            }  
        },  
    {  
      "template": "LookupComboBox",  
      "dataField": "dvCode",  
      "name": "dvCode",  
      "visibleIndex": 3,  
      "editorOptions": {  
        "name": "dvCode",  
        "dataCollectionOptions": "OrganizationDocumentVersions",  
        "displayExpr": "dvCode",  
        "valueExpr": "dvCode",  
        "placeholder": "Version"  
      },  
      "groupItemOptions": {  
        "name": "export",  
        "caption": "Export Options",  
        "colCount": 2  
      }  
    }  
  ]  
}'  
  
DECLARE @key_content int = 2  
  
SET @j = JSON_MODIFY  
    (  
        @j  
        --,CONCAT('strict $.content', QUOTENAME(@key_content), '.editorOptions.placeholder')  
        ,CONCAT('lax $.content', QUOTENAME(@key_content), '.editorOptions.placeholder')  
        ,NULL -- remove property  
    )  
  
SELECT @j as [@j], JSON_VALUE(@j, CONCAT('lax $.content', QUOTENAME(@key_content), '.editorOptions.placeholder')) as placeholder  
  
  
/*  
 Output  
{  
  "version": "1.0",  
  "content": [  
    {},  
    {  
      "colSpan": 2,  
      "visibleIndex": 1,  
      "dataField": "Path",  
      "name": "Path",  
      "editorType": "dxTextBox",  
      "editorOptions": {  
        "placeholder": "Directory"  
      },  
      "groupItemOptions": {  
        "name": "output",  
        "colCount": 2  
      }  
    },  
        {  
            "dataField": "Queue",  
            "name": "Queue",          
            "editorType": "dxTextBox",  
            "visibleIndex": 2,  
            "colSpan": 2,  
            "editorOptions": {  
                  
        "dataCollectionOptions": "OrganizationDocumentVersions",  
        "displayExpr": "dvCode",  
        "valueExpr": "dvCode",  
        "placeholder": "Version"  
      },  
      "groupItemOptions": {  
        "name": "export",  
        "caption": "Export Options",  
        "colCount": 2  
      }  
    }  
  ]  
}  
*/  

I think this is reported here, just 3 years ago, only.

f578bbad-5825-ec11-b6e6-000d3a4f0da0

anyone sees a way to modify and delete "placeholder": "MSMQ Queue"

Thank you in advance for your time and expertise.

Vladimir

Answer 1

I have found a workaround to this bug

I kept chipping away at this issue and JSON_MODIFY bug is only happening when an object has sole property that is being removed, for example

       "editorOptions": {  
         "placeholder": "Directory"  
       },  

but when there are other properties too, then JSON_MODIFY works

This can be taken advantage of by checking if there are other properties besides one being removed and if not, then the object itself is removed

Therefore, my workaround became this

DECLARE @key_content int = 2  
  
--SET @key_content = 3 -- removes property too  
  
IF NOT EXISTS -- placeholder is a single object property  
(  
    SELECT *  
    FROM OPENJSON(@j, CONCAT('lax $.content', QUOTENAME(@key_content), '.editorOptions'))  
    WHERE [key] != 'placeholder'  
)  
BEGIN  
    SET @j = JSON_MODIFY  
    (  
        @j  
        ,CONCAT('lax $.content', QUOTENAME(@key_content), '.editorOptions')  
        ,NULL -- remove object  
    )  
END  
ELSE  
BEGIN  
    SET @j = JSON_MODIFY  
    (  
        @j  
        ,CONCAT('lax $.content', QUOTENAME(@key_content), '.editorOptions.placeholder')  
        ,NULL -- remove property  
    )  
END  
  
  
SELECT @j as [@j], JSON_VALUE(@j, CONCAT('lax $.content', QUOTENAME(@key_content), '.editorOptions.placeholder')) as placeholder  

This worked for me.

when @Key _content = 3, then just property ["placeholder": "Version"] is removed.

Maybe this can help someone else with the same issue

Thanks
Vladimir