SQL Server update JSON with url in a property without introducing escaping sequences

Question

I am trying to update an array in a JSON contained into a column inside my DB, it's not clear to me what i did wrong but seems like that when a JSON property (a string) contain backslash, those value will be escaped.

I don't want that escaping is introduced into DB data, is there a way to prevent this or the only option is to proceed with a REPLACE() at the end of JSON modification?

Here the query :

 SELECT TOP 1  JSON_MODIFY(
           CASE WHEN ISNULL([_DOCUMENTS],'') = '' THEN '{}' ELSE [_DOCUMENTS] END ,
           '$.Documents',
           JSON_QUERY(
              (
              SELECT *
              FROM OPENJSON([_DOCUMENTS], '$.Documents') WITH (
                 Title nvarchar(1000) '$.Title',
                 Category nvarchar(1000) '$.Category',
                 NumberOfPages INT '$.NumberOfPages',
                 SizeInBytes INT '$.SizeInBytes',
                 MimeType nvarchar(1000) '$.MimeType',
                 [Date] nvarchar(1000) '$.Date',
                 ResourceId INT '$.ResourceId',
                 ResourceType INT '$.ResourceType',
                 ParentEntityType INT '$.ParentEntityType',
                 [Url] nvarchar(1000) '$.Url',
                 [Site] nvarchar(1000) '$.Site',
                 [Description] nvarchar(1000) '$.Description',
                 PreviewImageUrl nvarchar(1000) '$.PreviewImageUrl',
                 LanguageCode nvarchar(1000) '$.LanguageCode',
                 Sort INT '$.Sort'
              )
              WHERE LanguageCode != N'ru'
              FOR JSON PATH
              )
           )
        )
    FROM [dbo].[myTalbe]

Here the JSON before transformation :

{
    "ResourceId": 0,
    "ResourceType": 999,
    "ParentEntityId": 3,
    "ParentEntityType": 60,
    "Links": [],
    "ProvisionalMainImage": {
        "MimeType": "image/png",
        "ImageKind": 0,
        "Size": 0,
        "Annotation": null,
        "ImageGroup": "default",
        "ResourceId": 0,
        "ResourceType": 60,
        "ParentEntityId": 0,
        "ParentEntityType": 0,
        "Url": "",
        "Site": "",
        "Description": "",
        "PreviewImageUrl": "",
        "LanguageCode": "template",
        "Sort": 0
    },
    "Images": [],
    "YouTubeVideos": [],
    "Documents": [{
            "Title": "",
            "Category": "",
            "Author": "",
            "NumberOfPages": 0,
            "SizeInBytes": 161356,
            "MimeType": "application/pdf",
            "Date": "2000-01-01T00:00:00",
            "ResourceId": 0,
            "ResourceType": 70,
            "ParentEntityId": 0,
            "ParentEntityType": 0,
            "Url": "https://mySite/..",
            "Site": "",
            "Description": "",
            "PreviewImageUrl": "https://mySite/..",
            "LanguageCode": "pt",
            "Sort": 0
        }, {
            "Title": "",
            "Category": "",
            "Author": "",
            "NumberOfPages": 0,
            "SizeInBytes": 192958,
            "MimeType": "application/pdf",
            "Date": "2000-01-01T00:00:00",
            "ResourceId": 0,
            "ResourceType": 70,
            "ParentEntityId": 0,
            "ParentEntityType": 0,
            "Url": "https://mySite/..",
            "Site": "",
            "Description": "",
            "PreviewImageUrl": "https://mySite/..",
            "LanguageCode": "ru",
            "Sort": 0
        }, {
            "Title": "",
            "Category": "",
            "Author": "",
            "NumberOfPages": 0,
            "SizeInBytes": 162314,
            "MimeType": "application/pdf",
            "Date": "2000-01-01T00:00:00",
            "ResourceId": 0,
            "ResourceType": 70,
            "ParentEntityId": 0,
            "ParentEntityType": 0,
            "Url": "https://mySite/..",
            "Site": "",
            "Description": "",
            "PreviewImageUrl": "https://mySite/..",
            "LanguageCode": "template",
            "Sort": 0
        }
    ],
    "Htmls": []
}

Here the result after the query do its transofrmation :

{
    "ResourceId": 0,
    "ResourceType": 999,
    "ParentEntityId": 3,
    "ParentEntityType": 60,
    "Links": [],
    "ProvisionalMainImage": {
        "MimeType": "image/png",
        "ImageKind": 0,
        "Size": 0,
        "Annotation": null,
        "ImageGroup": "default",
        "ResourceId": 0,
        "ResourceType": 60,
        "ParentEntityId": 0,
        "ParentEntityType": 0,
        "Url": "",
        "Site": "",
        "Description": "",
        "PreviewImageUrl": "",
        "LanguageCode": "template",
        "Sort": 0
    },
    "Images": [],
    "YouTubeVideos": [],
    "Documents": [{
            "Title": "",
            "Category": "",
            "NumberOfPages": 0,
            "SizeInBytes": 161356,
            "MimeType": "application\/pdf",
            "Date": "2000-01-01T00:00:00",
            "ResourceId": 0,
            "ResourceType": 70,
            "ParentEntityType": 0,
            "Url": "https:\/\/mySite\/..",
            "Site": "",
            "Description": "",
            "PreviewImageUrl": "https:\/\/mySite\/..",
            "LanguageCode": "pt",
            "Sort": 0
        }, {
            "Title": "",
            "Category": "",
            "NumberOfPages": 0,
            "SizeInBytes": 162314,
            "MimeType": "application\/pdf",
            "Date": "2000-01-01T00:00:00",
            "ResourceId": 0,
            "ResourceType": 70,
            "ParentEntityType": 0,
            "Url": "https:\/\/mySite\/..",
            "Site": "",
            "Description": "",
            "PreviewImageUrl": "https:\/\/mySite\/..",
            "LanguageCode": "template",
            "Sort": 0
        }
    ],
    "Htmls": []
}

As you can see the wrong '\ /' sequence is introduced at some point by the function used (one of them probably do the escaping thing). I can easily revert '\ /' back to '/' with just a REPLACE operation. But that approach seems risky to me, if there are other escaping i didn't see in my testing. I prefer to understand how to use the function above, to manupulate JSON without introducing escaping artifacts.

Answer 1

If I understand the question correctly, you are trying to delete items from the $.Documents JSON array, based on a specific condition. The documentation explains, that ... If the source data contains special characters, the FOR JSON clause escapes them in the JSON output with '\' ..., so a possible approach is to parse this JSON array with OPENJSON() and default schema, and rebuild it again using string aggregation:

SELECT JSON_MODIFY(
   [_DOCUMENTS],  
   '$.Documents',
   JSON_QUERY((
      SELECT CONCAT('[', STRING_AGG([value], ','), ']')
      FROM OPENJSON([_DOCUMENTS], '$.Documents')
      WHERE JSON_VALUE([value], '$.LanguageCode') != 'ru'
   ))
)
FROM [dbo].[myTalbe]