How to write a stored procedure in the cosmos database to do an update on a document

Question

I observed that we cannot write a UPDATE query on a container like SELECT on a query editor in the azure portal. I want to right a reusable code that can be used to reset my document data.

Could one please let me know if writing a stored procedure is the right approach to achieve this.

I'm trying to update a document say,

{ "partitionKey": "98776632-34er-ii76-318a-9876543456ae", "customer": { "firstName": "UserFirstName", "lastName": "UserLastName", "email": "user@gmail.com", "password": "#4Ftyer%4s" }, "balance": 400 }

Update c 
SET c.balance = 0, c.customer.password="ResetPasswordValue" 
Where c.partitionKey = "98776632-34er-ii76-318a-9876543456ae"

This doesn't work from a query editor, as update queries are not allowed

Could one please let me know how to get this implement using a stored procedure. Thank you

FYI there are several questions (and answers) directly related to yours, which explain that the only operation via query language is `SELECT` - such as [this answer](https://stackoverflow.com/a/46873986/272109) I wrote a while back. Even though this isn't an `exact` duplicate, I marked it a duplicate of the question related to that answer, since it's the same basic issue. — David Makogon, Jan 13 '21 at 12:20

Tiny Wang · Accepted Answer · 2021-01-13T02:52:29.247

First of all, COSMOSDB hasn't supported partial updates on document until now, your test also proved this. Here's a highly voted request and hasn't got any update.

So I have an idea that deleting and insert a new document instead.

==================UPDATE======================

I tried this code

function example(param) {
    var collection = getContext().getCollection();
    var containerLink = collection.getSelfLink(); 
    var documentQuery ={
                    'query': 'SELECT * FROM c where c.category = @param ',
                    'parameters': [{ 'name': '@param', 'value': param }]
                };
    
    collection.queryDocuments(containerLink, documentQuery,
        function (err, items) {
            console.log(JSON.stringify(items));
            items.forEach(element => {
                element.name="bac";
                collection.replaceDocument(element._self, element, function (err) {
                    if (err) throw err;
                });
            });
        }
    );
}

How to write a stored procedure in the cosmos database to do an update on a document

1 Answers1