Azure Cosmos DB Pre-Trigger

Question

For external reasons we can't save datetimes on the Azure Cosmos DB, so I'm trying to order the collections on the database using another approach.

So, I've created a trigger to update a field from the item I'm storing executed on Pre.

Here is the trigger:

function trigger() {
    var context = getContext();
    
    var container = context.getCollection();
    var collectionLink = container.getSelfLink();
    
    var request = context.getRequest();
    var chatToCreate = request.getBody();
 
    var query = {
        "query": 'SELECT VALUE MAX(c.chatOrder) FROM Chats c WHERE c.user.id = @userId',
        "parameters": [{
            "name": "@userId", 
            "value": chatToCreate.user.id
        }]
    };

//var query = "SELECT VALUE MAX(c.chatOrder) FROM c WHERE c.user.id = '" + chatToCreate.user.id + "'";
 
    var requestOptions = { partitionKey: chatToCreate.partitionKey };
    var isAccepted = container.queryDocuments(collectionLink, query, requestOptions, function (err, items, responseOptions) {
        if (err) throw new Error('Error querying items: ' + err.message);
 
        var currentMaxOrder = items[0];
        var newOrder = currentMaxOrder ? currentMaxOrder + 1 : 1;
        chatToCreate.chatOrder = newOrder;
 
        request.setBody(chatToCreate);
    });
 
    if (!isAccepted) throw new Error('Unable to update order, abort.');
}

The trigger is just checking if the same user has some other chats on the database an tries to update its order depending on how many founds out.

It's called from a C# API like:

ItemResponse<Chat> createChatResponse = await chatContainer.CreateItemAsync(chat, new PartitionKey(chat.Id), new ItemRequestOptions
                { PreTriggers = new List<string> { "SetOrder" } });

I've checked it and chatToCreate.user.id as a GUID (it's not empty nor null). I've tried many combinations Pre/Post trigger, escaping chars, changing the query (select another property or force and id on the where clause).

The same trigger on a local Cosmos DB emulator is working fine. It seems like the SELECT is not returning anything.

Answer 1

With your current approach you might run into concurrency issues when multiple items are added at the same moment. Another approach you can take is to keep one document in the container that maintains a cursor. Then using the patch methods you can simultaneously increment and retrieve the value to use for uploading the next document.

//run once to create the item with starting index
var cursor = new Cursor()
{
    Id = "Example",
    Current = -1,
};
await container.CreateItemAsync(cursor);

//increment the value and return the new value
var operations = new List<PatchOperation>
{
    PatchOperation.Increment("/current", 1),
};
var response = await container.PatchItemAsync<Cursor>(cursor.Id, new(...), operations);

//next value is in response.Resource.Current

It's rather expensive though as you'll incur ~11 RU each time you increment the value. It might still cause conflicts when using multi region writes.