A bug in JSON_MODIFY?

Vladimir Moldovanenko 251 Reputation points
2022-07-13T00:40:03.73+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,519 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Vladimir Moldovanenko 251 Reputation points
    2022-07-13T15:05:49.9+00:00

    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

    0 comments No comments