How to find into mongodb to the last item of an array?

Question

I want to find documents where last elements in an array equals to some value. Array elements may be accessed by specific array position:

// i.e. comments[0].by == "Abe"
db.example.find( { "comments.0.by" : "Abe" } )

but how do i search using the last item as criteria? i.e.

db.example.find( { "comments.last.by" : "Abe" } )

By the way, i'm using php

Answer 1

I know this question is old, but I found it on google after answering a similar new question. So I thought this deserved the same treatment.

You can avoid the performance hit of $where by using aggregate instead:

db.example.aggregate([

    // Use an index, which $where cannot to narrow down
    {$match: { "comments.by": "Abe" }},

    // De-normalize the Array
    {$unwind: "$comments"},

    // The order of the array is maintained, so just look for the $last by _id
    {$group: { _id: "$_id", comments: {$last: "$comment"} }},

    // Match only where that $last comment by `by.Abe`
    {$match: { "comments.by": "Abe" }},

    // Retain the original _id order
    {$sort: { _id: 1 }}

])

And that should run rings around $where since we were able to narrow down the documents that had a comment by "Abe" in the first place. As warned, $where is going to test every document in the collection and never use an index even if one is there to be used.

Of course, you can also maintain the original document using the technique described here as well, so everything would work just like a find().

Just food for thought for anyone finding this.

---

Update for Modern MongoDB releases

Modern releases have added the $redact pipeline expression as well as $arrayElemAt ( the latter as of 3.2, so that would be the minimal version here ) which in combination would allow a logical expression to inspect the last element of an array without processing an $unwind stage:

db.example.aggregate([
  { "$match": { "comments.by": "Abe" }},
  { "$redact": {
    "$cond": {
      "if": {
        "$eq": [
          { "$arrayElemAt": [ "$comments.by", -1 ] },
          "Abe"
        ]
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }}
])

The logic here is done in comparison where $arrayElemAt is getting the last index of the array -1, which is transformed to just an array of the values in the "by" property via $map. This allows comparison of the single value against the required parameter, "Abe".

Or even a bit more modern using $expr for MongoDB 3.6 and greater:

db.example.find({
  "comments.by": "Abe",
  "$expr": {
    "$eq": [
      { "$arrayElemAt": [ "$comments.by", -1 ] },
      "Abe"
    ]
  }
})

This would be by far the most performant solution for matching the last element within an array, and actually expected to supersede the usage of $where in most cases and especially here.