Delete Documents from CosmosDB based on condition through Query Explorer

Question

What's the query or some other quick way to delete all the documents matching the where condition in a collection?
I want something like DELETE * FROM c WHERE c.DocumentType = 'EULA' but, apparently, it doesn't work.

Note: I'm not looking for any C# implementation for this.

Solution 1

This is a bit old but just had the same requirement and found a concrete example of what @Gaurav Mantri wrote about.

The stored procedure script is here:

https://social.msdn.microsoft.com/Forums/azure/en-US/ec9aa862-0516-47af-badd-dad8a4789dd8/delete-multiple-docdb-documents-within-the-azure-portal?forum=AzureDocumentDB

Go to the Azure portal, grab the script from above and make a new stored procedure in the database->collection you need to delete from.

Then right at the bottom of the stored procedure pane, underneath the script textarea is a place to put in the parameter. In my case I just want to delete all so I used:

SELECT c._self FROM c

I guess yours would be:

SELECT c._self FROM c WHERE c.DocumentType = 'EULA'

Then hit 'Save and Execute'. Viola, some documents get deleted. After I got it working in the Azure Portal I switched over the Azure DocumentDB Studio and got a better view of what was happening. I.e. I could see I was throttled to deleting 18 a time (returned in the results). For some reason I couldn't see this in the Azure Portal.

Anyway, pretty handy even if limited to a certain amount of deletes per execution. Executing the sp is also throttled so you can't just mash the keyboard. I think I would just delete and recreate the Collection unless I had a manageable number of documents to delete (thinking <500).

Props to Mimi Gentz @Microsoft for sharing the script in the link above.

HTH

Solution 2

I want something like DELETE * FROM c WHERE c.DocumentType = 'EULA' but, apparently, it doesn't work.

Deleting documents this way is not supported. You would need to first select the documents using a SELECT query and then delete them separately. If you want, you can write the code for fetching & deleting in a stored procedure and then execute that stored procedure.

Solution 3

I wrote a script to list all the documents and delete all the documents, it can be modified to delete the selected documents as well.

var docdb = require("documentdb");
var async = require("async");

var config = {
  host: "https://xxxx.documents.azure.com:443/",
  auth: {
    masterKey: "xxxx"
  }
};

var client = new docdb.DocumentClient(config.host, config.auth);

var messagesLink = docdb.UriFactory.createDocumentCollectionUri("xxxx", "xxxx");

var listAll = function(callback) {
  var spec = {
    query: "SELECT * FROM c",
    parameters: []
  };

  client.queryDocuments(messagesLink, spec).toArray((err, results) => {
    callback(err, results);
  });
};

var deleteAll = function() {
  listAll((err, results) => {
    if (err) {
      console.log(err);
    } else {
      async.forEach(results, (message, next) => {
        client.deleteDocument(message._self, err => {
          if (err) {
            console.log(err);
            next(err);
          } else {
            next();
          }
        });
      });
    }
  });
};

var task = process.argv[2];
switch (task) {
  case "listAll":
    listAll((err, results) => {
      if (err) {
        console.error(err);
      } else {
        console.log(results);
      }
    });
    break;
  case "deleteAll":
    deleteAll();
    break;

  default:
    console.log("Commands:");
    console.log("listAll deleteAll");
    break;
}

Solution 4

And if you want to do it in C#/Dotnet Core, this project may help: https://github.com/lokijota/CosmosDbDeleteDocumentsByQuery. It's a simple Visual Studio project where you specify a SELECT query, and all the matches will be a) backed up to file; b) deleted, based on a set of flags.

View more solutions

Comments

[Tom Sun - MSFT]

As Gaurav Mantri mentioned, it is not supported currently. There is a feedback under review.

[Jesse Carter]

Interestingly this is possible using the Graph APIs as you can do .drop() on the result of a traversal to remove all selected docs....

[GorvGoyl]

@JesseCarter could u describe in a bit detail

[Jesse Carter]

@JerryGoyal Unfortunately unless you're fully embracing graph I don't think it will work for your usecase as Cosmos expects a specific document format. But for example you could do something like g.V().has('DocumentType', eq('EULA')).drop() and clear out all docs that matched

[GorvGoyl]

where do i run this query

[sɐunıɔןɐqɐp]

Welcome to Stack Overflow! While links are great way of sharing knowledge, they won't really answer the question if they get broken in the future. Add to your answer the essential content of the link which answers the question. In case the content is too complex or too big to fit here, describe the general idea of the proposed solution. Remember to always keep a link reference to the original solution's website. See: How do I write a good answer?

[Gopal Krishnan]

Thanks for this, it is helpful when you need to run it against a single partition. But where the query itself is cross partition the stored procedure option is not feasible as you can only run a store proc against a partition currently.

[João Pedro 'jota' Martins]

Can you clarify what this is? This looks like Spark code (.rdd), not plain Python.

[Neo]

i have code for select now that selected data i want to delete from cosmos how to do that please guide with code snippet