Set based operations (INSERT, UPDATE, DELETE)
It would be nice to be able to update parts of documents, delete documents, and insert documents using set based operations, similar to SQL.
UPDATE Users SET IsActive = 0 WHERE LastLogin < '2010-01-01'
DELETE FROM Users WHERE LastLogin < '2009-01-01'
INSERT INTO table2 SELECT * FROM table1
There are multiple responses to this suggestion.
Support for SQL-like syntax for UPDATE, DELETE and INSERT INTO are not on our road map at this time.
However, the underlying functionality for an UPDATE, SET is essentially a PATCH. This functionality we are actively working on. See this User Voice item here to track for future updates. https://feedback.azure.com/forums/263030-azure-cosmos-db/suggestions/6693091-be-able-to-do-partial-updates-on-document
Thank you for your suggestion.
Eric P commented
Looks like this feature is not on the roadmap. So I started developing a tool to simplify doing bulk updates, deletes and copying data.
Here is a github project:
If anyone is interested in colaborating on this, please reach out to me: email@example.com.
Oliver Münchow (AixConcept GmbH) commented
Within the data explorer you can only filter the data but you can not simply update or delete data through the explorer. Would be great if you could write something like
delete from c where c.name = "Robert"
update c set c.name = "Paul" where c.name = "Robert"
We need this. And also is there a way to delete all documents?
Also a truncate command would be helpful.
Grahame Horner commented
This would be great for server side transformation of relational database structures into json documents where the relation data has been imported into CosmosDB but requires denormilisation to give performance and make better use of nosql concepts
Dave Bown commented
I agree this is a great to have feature but I think it's very important if you do it that you can turn this on and off on collections rather than it just being turned on b default. Maybe even assigning it as a specific right to certain users would be a good idea to increase flexibility.
Thinking about the SQL injection attacks that will all of a sudden maybe become possible in some peoples apps not doing things right. Some of us may also just want to turn it off to reduce risks of such attacks.
Søren Enemærke commented
We need an update on this (as it has been more than a year since the UNDER REVIEW) was added and almost one and a half year since it was proposed.
David Isaacs Paternostro commented
Is there any update on whether this is now possible? It is essential for many using a database I presume.
Brice Vandeputte commented
"DELETE WHERE" seems really important for me too. related topic on SO : http://stackoverflow.com/questions/29137708/how-to-delete-all-the-documents-in-documentdb-through-c-sharp-code/29139423#29139423
This becomes even more important under GA, where, I'm informed that
a) every collection will be billed a minimum of one hour
b) a maximum of 5 collections can be created per minute.
In the acceptance testing phase of our CI/CD process we delete and recreate a collection to ensure it's empty. This is much easier (and faster) than listing and iteratively deleting each type (document, udf, s-proc, trigger). This will soon cost us an hour for each invocation. Having the ability to Set, or better, wildcard delete will be critical.