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.

[`$last`](https://mongodb.github.io/mongo-csharp-driver/2.18/reference/driver/expressions/#last)? — rickhg12hs, Nov 18 '22 at 21:31

R2D2 · Answer 1 · 2022-11-18T21:26:51.800

1

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

edited Nov 18 '22 at 21:26

answered Nov 18 '22 at 21:21

R2D2

9,410
2
12
28

yes, but the question is how to do it in C#... – Mahdi Tahsildari Nov 18 '22 at 21:48
ah , now I see: https://jira.mongodb.org/browse/CSHARP-2084 – R2D2 Nov 18 '22 at 21:53
see https://stackoverflow.com/questions/68395490/how-to-use-raw-mongodb-aggregation-query-in-c/68397480#68397480 – dododo Nov 18 '22 at 22:43

score 0 · Accepted Answer · answered Nov 20 '22 at 12:49

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.

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

2 Answers2