Cosmos DB - Deleting a document

Question

How can I delete an individual record from Cosmos DB?

I can select using SQL syntax:

SELECT *
FROM collection1
WHERE (collection1._ts > 0)

And sure enough all documents (analogous to rows?) are returned

However this doesn't work when I attempt to delete

DELETE
FROM collection1
WHERE (collection1._ts > 0)

How do I achieve that?

Biddick

Answer 1

The DocumentDB API's SQL is specifically for querying. That is, it only provides SELECT, not UPDATE or DELETE.

Those operations are fully supported, but require REST (or SDK) calls. For example, with .net, you'd call DeleteDocumentAsync() or ReplaceDocumentAsync(), and in node.js, this would be a call to deleteDocument() or replaceDocument().

In your particular scenario, you could run your SELECT to identify documents for deletion, then make "delete" calls, one per document (or, for efficiency and transactionality, pass an array of documents to delete, into a stored procedure).

Etching

Ah ok, another piece of the puzzle falls into place. I'm using Python and can see DeleteDocument(). Thanks! – Biddick

is this possible for java version of SDK? – Miss

@Miss - yes, this is fully supported in Java. You'll find a deleteDocument() method of DocumentClient. – Etching

@DavidMakogon Can deleteDocument be used without partition key? I am using a stored procedure but it is not working. – Herries

Answer 2

The easiest way is probably by using Azure Storage Explorer. After connecting you can drill down to a container of choice, select a document and then delete it. You can find additional tools for Cosmos DB on https://gotcosmos.com/tools.

Kickapoo

Is there a way to delete multiple documents at once in Azure Storage Explorer? – Proportional

Even easier, use the Data Explorer built into the Azure Portal web page. – Bonehead

the Data Explorer built into the Azure Portal web page doesn't understand delete – Jolo

Answer 3

Another option to consider is the time to live (TTL). You can turn this on for a collection and then set an expiration for the documents. The documents will be cleaned up automatically for you as they expire.

Intellectualize

easiest solution i've found so far – Ionopause

Doesn't work for me. After settings TTL to 1sec. the documents were not visible, but after going back to TTL turned off the documents appeared again. – Forbid

@DOminikMinc maybe you need to wait little longer. It takes some time for the cloud to remove the items. – Drusie

Answer 4

Create a stored procedure with the following code:

/**
 * A Cosmos DB stored procedure that bulk deletes documents for a given query.
 * Note: You may need to execute this stored procedure multiple times (depending whether the stored procedure is able to delete every document within the execution timeout limit).
 *
 * @function
 * @param {string} query - A query that provides the documents to be deleted (e.g. "SELECT c._self FROM c WHERE c.founded_year = 2008"). Note: For best performance, reduce the # of properties returned per document in the query to only what's required (e.g. prefer SELECT c._self over SELECT * )
 * @returns {Object.<number, boolean>} Returns an object with the two properties:
 *   deleted - contains a count of documents deleted
 *   continuation - a boolean whether you should execute the stored procedure again (true if there are more documents to delete; false otherwise).
 */
function bulkDeleteStoredProcedure(query) {
    var collection = getContext().getCollection();
    var collectionLink = collection.getSelfLink();
    var response = getContext().getResponse();
    var responseBody = {
        deleted: 0,
        continuation: true
    };

    // Validate input.
    if (!query) throw new Error("The query is undefined or null.");

    tryQueryAndDelete();

    // Recursively runs the query w/ support for continuation tokens.
    // Calls tryDelete(documents) as soon as the query returns documents.
    function tryQueryAndDelete(continuation) {
        var requestOptions = {continuation: continuation};

        var isAccepted = collection.queryDocuments(collectionLink, query, requestOptions, function (err, retrievedDocs, responseOptions) {
            if (err) throw err;

            if (retrievedDocs.length > 0) {
                // Begin deleting documents as soon as documents are returned form the query results.
                // tryDelete() resumes querying after deleting; no need to page through continuation tokens.
                //  - this is to prioritize writes over reads given timeout constraints.
                tryDelete(retrievedDocs);
            } else if (responseOptions.continuation) {
                // Else if the query came back empty, but with a continuation token; repeat the query w/ the token.
                tryQueryAndDelete(responseOptions.continuation);
            } else {
                // Else if there are no more documents and no continuation token - we are finished deleting documents.
                responseBody.continuation = false;
                response.setBody(responseBody);
            }
        });

        // If we hit execution bounds - return continuation: true.
        if (!isAccepted) {
            response.setBody(responseBody);
        }
    }

    // Recursively deletes documents passed in as an array argument.
    // Attempts to query for more on empty array.
    function tryDelete(documents) {
        if (documents.length > 0) {
            // Delete the first document in the array.
            var isAccepted = collection.deleteDocument(documents[0]._self, {}, function (err, responseOptions) {
                if (err) throw err;

                responseBody.deleted++;
                documents.shift();
                // Delete the next document in the array.
                tryDelete(documents);
            });

            // If we hit execution bounds - return continuation: true.
            if (!isAccepted) {
                response.setBody(responseBody);
            }
        } else {
            // If the document array is empty, query for more documents.
            tryQueryAndDelete();
        }
    }
}

And execute it using your partition key (example: null) and a query to select the documents (example: SELECT c._self FROM c to delete all).

Based on Delete Documents from CosmosDB based on condition through Query Explorer

Alvie

@marc_s do I need to use the same format for the query (e.g. SELECT c._self FROM c) or can I do (SELECT * FROM c). In addition, is the partition key the name of the partition key? – Beheld

Trying this for hours now. Works great if I call it from the Azure portal, and does not fail when I call it from a client C# app. But never deletes any records. What am I doing wrong? Why can't I delete records from my client app? Any examples incl. c# client code? – Pavement

Unable to delete any record using this. Returns 0 docs deleted. – Herries

This does not work from either the Azure portal or the Cosmos emulator. It returns 0 records. – Lecompte

Answer 5

Here is an example of how to use bulkDeleteStoredProcedure using .net Cosmos SDK V3.

ContinuationFlag has to be used because of the execution bounds.

private async Task<int> ExecuteSpBulkDelete(string query, string partitionKey)
    {
        var continuationFlag = true;
        var totalDeleted = 0;
        while (continuationFlag)
        {
            StoredProcedureExecuteResponse<BulkDeleteResponse> result = await _container.Scripts.ExecuteStoredProcedureAsync<BulkDeleteResponse>(
                "spBulkDelete", // your sproc name
                new PartitionKey(partitionKey), // pk value
                new[] { sql });

            var response = result.Resource;
            continuationFlag = response.Continuation;
            var deleted = response.Deleted;
            totalDeleted += deleted;
            Console.WriteLine($"Deleted {deleted} documents ({totalDeleted} total, more: {continuationFlag}, used {result.RequestCharge}RUs)");
        }

        return totalDeleted;
    }

and response model:

public class BulkDeleteResponse
{
    [JsonProperty("deleted")]
    public int Deleted { get; set; }

    [JsonProperty("continuation")]
    public bool Continuation { get; set; }
}

Seeing

Answer 6

You can delete a single document from cosmos db using c# by below method - You can use the below method with cosmosdb container instance.

CustomerContainer.DeleteItemAsync(id, new PartitionKey("AnyValueofPartitionKey"))

T -> Is the type of item in the container.
id -> Is the guid of the item to be deleted. (for this you can first fetch the items from cosmosdb using select query)
AnyValueofPartitionKey - Most of the times we create Partition Key with cosmos db containers, so in this you have to provide a value for that key for ex - My key is customerCity, so I provided "Goa".

Cymophane

Answer 7

If you are using the Node.JS API, you can use the command:

container.item(id, partitionId).delete()

Waterless