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.

CodePudding user response：

Option 1) Find() -> expected to be faster

db.collection.find({
 $expr: {
  $eq: [
  {
    $arrayElemAt: [
      "$history.status",
      -1
    ]
    },
    2
  ]
 }
})

Playground1

Option 2) Aggregation

db.collection.aggregate([
 {
"$addFields": {
  last: {
    $arrayElemAt: [
      "$history",
      -1
    ]
   }
  }
},
{
  $match: {
    "last.status": 2
  }
},
{
 $project: {
  "history": 1
  }
 }
])

Playground2

CodePudding user response：

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.