Cannot delete Cosmos DB Item with Illegal Id

Question

Three items in our Cosmos DB got created with an illegal id, we can Get the items via code but we are unable to update or delete to fix the issue. How can we get these out of our database?

Answer 1

You can delete the documents with invalid IDs server via a stored procedure which can be executed from the v3 SDK without downgrading the v2 SDK version e.g.:

function deleteMyDoc(docId) {  
    var collection = getContext().getCollection();  
  
    var query =  
    {  
        'query' : 'SELECT * FROM c where c.id = @docId',  
        'parameters' : [{'name':'@docId', 'value': docId}]   
    };  
      
    if(!collection.queryDocuments(  
        collection.getSelfLink(),  
        query,  
        function (err, docs, options) {  
            if (err) throw err;  
            if (!docs || docs.length !== 1) {  
                var response = getContext().getResponse();  
                response.setBody('Document Not Found');  
            }  
            else {  
                var suppression = docs[0];  
                if(!collection.deleteDocument(suppression._self, {etag: suppression.etag})) {  
                    throw new Error('Delete was not accepted by the server.');  
                }  
  
                var response = getContext().getResponse();  
                    response.setBody("Document Deleted");  
                }  
            }  
        )){  
             throw new Error('The query was not accepted by the server.');  
        }  
    }  

An then execute from c#

var invalidId = "grabage/junk";  
await container.Scripts.ExecuteStoredProcedureAsync<string>("deleteMyDoc", new PartitionKey("my partition key"), new[] { invalidId });  







  
  

Answer 2

Hi @Nicole Cortes Thank you for posting your Question on Microsoft Q&A.

Resource Id should not contain the properties - '/', '\', '?', '#'

https://learn.microsoft.com/en-us/dotnet/api/microsoft.azure.documents.resource.id?view=azure-dotnet

Invalid character in an item ID
An item is inserted into Azure Cosmos DB with an invalid character in the item ID.

Solution:
Change the ID to a different value that doesn't contain the special characters. If changing the ID isn't an option, you can Base64 encode the ID to escape the special characters. Base64 can still produce a name with a invalid character '/' which needs to be replaced.

Items already inserted in the container for the ID can be replaced by using RID values instead of name-based references.

Please let me know if that help. If not we can discuss further on possible solutions.

Regards,
Oury

Answer 3

Copied from this SO answer: https://stackoverflow.com/a/67268470/732673

1. Set the TTL property on the container to "On" (default is Off). What this setting mean is that documents in the container will automatically delete if TTL is specified on the document and once the TTL has expired. This you can set through Data Explorer in Azure Portal as shown below:

2. Update the document and set the TTL property on the document. You can do so by using Cosmos DB SDK. See sample code where it's set to 30 seconds (you can set it as low as 1 second). Once the upsert operation succeeds, you will see the document gets automatically deleted after TTL has expired on the document (30 seconds in my case). static async Task UpdateItemTtl()
   {
   CosmosClient cosmosClient = new CosmosClient(connectionString);
   Container container = cosmosClient.GetContainer(databaseName, containerName);
   string documentBody = "{\"ttl\": 30, \"Code\": \"https://CokeURL.com/gbm330\", \"Quantity\": 1, \"ActivityId\": \"0000\", \"id\": \"https://CokeURL.com/gbm330\"}";
   Object o = JsonConvert.DeserializeObject(documentBody);
   ItemResponse<object> response = await container.UpsertItemAsync(o, new PartitionKey("0000"));
   documentBody = JsonConvert.SerializeObject(response.Resource);
   Console.WriteLine(documentBody);
   }

This still requires some code unfortunately because of the bug in Azure Portal where you can't access or delete the docs.