CODEWITHSUNDEEP
azurestored-proceduresazure-cosmosdb
Eugenio
Eugenio

Reputation: 1053

Azure CosmosDB: stored procedure delete documents based on query

The goal is to input a simple string query like

SELECT * 
FROM c 
WHERE c.deviceId = "device1"

and all resulting fetched documents need to be deleted.

I have found very old posts about doing this with a stored procedure, but I can't get it to work properly with the "new" UI.

Thanks a lot in advance.

EDIT: I feel like @jay-gong pointed to the correct direction but I encountered a problem with his solution:

I can correctly create the stored procedure but when I try to execute it it asks for the partition key, which I give but after executing, it doesn't delete any document.

The collection just has a few documents and its partition key is /message/id which is what I wrote in the partition key field.

Upvotes: 6

Views: 20050

Answers (1)

Jay Gong
Jay Gong

Reputation: 23782

Since cosmos db does not support deleting documents by SQL (Delete SQL for CosmosDB), you could query the documents and delete them by Delete SDK one by one. Or you could choose bulk operation in stored procedure.

You could totally follow the stored procedure bulk delete sample code to implement your requirements which works for me.

function bulkDeleteProcedure(query) {
    var collection = getContext().getCollection();
    var collectionLink = collection.getSelfLink();
    var response = getContext().getResponse();
    var responseBody = {
        deleted: 0,
        continuation: true
    };

    query = 'SELECT * FROM c WHERE c.deviceId="device1"';

    // 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();
        }
    }
}

Furthermore, as I know, stored procedure has 5 seconds execute limitation. If you crash into the time out error, you could pass the continuation token as parameter into stored procedure and execute stored procedure several times.

Update Answer:

Partition key is necessary for the partitioned collection in the stored procedure.(Please refer to the detailed explanation :Azure Cosmos DB asking for partition key for stored procedure.)

So, firstly,above code needs your partition key.For example, your partition key is defined as /message/id and your data as below:

{
    "message":{
        "id":"1"
    }
}

Then you need to pass the pk as message/1.

Obviously,your query sql crosses partitions,I suggest you adopt http trigger azure function instead of stored procedure.In that function,you could use cosmos db sdk code to do the query and delete operations.Don't forget set the EnableCrossPartitionQuery to true. Please refer to this case:Azure Cosmos DB asking for partition key for stored procedure.

Upvotes: 15

Related Questions