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?

Solution 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).

Solution 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.

Solution 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.

Solution 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

View more solutions

Comments

[Ben Mayo]

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

[Luke W]

easiest solution i've found so far

[blackuprise]

is this possible for java version of SDK?

[David Makogon]

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

[Dominik Minc]

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.

[Ash A]

@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?

[jschober]

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?

[fascynacja]

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

[Anurag]

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

[Anurag]

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

[user2233706]

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

[Petru Lutenco]

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