Azure CosmosDB stored procedure to update all items' field

Question

Let's say we have a container with following items

{
  location:CA,
  bool: false
}

{
  location:CA,
  bool: false
}

{
  location:CA,
  bool: false
}

How do we write a stored procedure to query all items and update all item's bool field from false to true? I have not find any reference yet and I know CosmosDB stored procedure only support querying not updating or deleting.

What's your partition key? `/id`? And Cosmos DB stored procedure supports updating and deleting. — Steve Johnson, Feb 09 '21 at 01:37
Stored procedure can't achieve your requirement. You can do this by SDK. — Steve Johnson, Feb 09 '21 at 01:49
@SteveZhao Stored procedure cannot achieve such simple tasks? How about just update 1 item? You do not need to exactly "update" right? You can "update" by replacing the whole document? — szastronaut, Feb 09 '21 at 01:52
Because stored procedure is scoped to a logic partition. You have three logic partition in your case. Just update 1 item is possible. — Steve Johnson, Feb 09 '21 at 01:57
@SteveZhao My bad, I changed the question. Only 1 logical partition is totally fine. — szastronaut, Feb 09 '21 at 02:01

score 2 · Accepted Answer · answered Feb 09 '21 at 02:32

This stored procedure code:

// SAMPLE STORED PROCEDURE
function sample() {
    var collection = getContext().getCollection();

    // Query all documents in one logic partition
    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT * FROM root r',
    function (err, feed, options) {
        if (err) throw err;

        if (!feed || !feed.length) {
            var response = getContext().getResponse();
            response.setBody('no docs found');
        }
        else {
            feed.forEach(element => {
                element.bool = true;
                collection.replaceDocument(element._self, element,function (err){
                    if (err) throw err;
                })
            })
            var response = getContext().getResponse();
            response.setBody("replace success");
        }
    });

    if (!isAccepted) throw new Error('The query was not accepted by the server.');
}

Execute stored procedure:

BTW, your partition key should be /location.

Azure CosmosDB stored procedure to update all items' field

1 Answers1