C# MongoDB query: filter based on the last item of array

Question

I have a MongoDB collection like this:

{
    _id: "abc",
    history: 
    [
        {
            status: 1,
            reason: "confirmed"
        },
        {
            status: 2,
            reason: "accepted"
        }
    ],
    _id: "xyz",
    history: 
    [
        {
            status: 2,
            reason: "accepted"
        },
        {
            status: 10,
            reason: "cancelled"
        }
    ]
}

I want to write a query in C# to return the documents whose last history item is 2 (accepted). So in my result I should not see "xyz" because its state has changed from 2, but I should see "abc" since its last status is 2. The problem is that getting the last item is not easy with MongoDB's C# driver - or I don't know how to.

I tried the linq's lastOrDefault but got System.InvalidOperationException: {document}{History}.LastOrDefault().Status is not supported error.

I know there is a workaround to get the documents first (load to memory) and then filter, but it is client side and slow (consumes lot of network). I want to do the filter on server.

Answer 1

I found a hackaround: to override the history array with the last history document, then apply the filter as if there was no array. This is possible through Aggregate operation $addFields.

PipelineDefinition<Process, BsonDocument> pipeline = new BsonDocument[]
{
    new BsonDocument("$addFields",
        new BsonDocument("history",
            new BsonDocument ( "$slice",
                new BsonArray { "$history",  -1 }
            )
        )
    ),
    new BsonDocument("$match",
       new BsonDocument
       {
           { "history.status", 2 }
       }
    )
};

var result = collection.Aggregate(pipeline).ToList();

result will be the documents with last history of 2.