Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 247
fnaquira Avatar asked May 10 '12 21:05

fnaquira


People also ask

How do I get the last element in MongoDB?

To find last object in collection, at first sort() to sort the values. Use limit() to get number of values i.e. if you want only the last object, then use limit(1).

How do I search an array in MongoDB?

To search the array of object in MongoDB, you can use $elemMatch operator. This operator allows us to search for more than one component from an array object.

How do you check if an array contains a value in MongoDB?

To query if the array field contains at least one element with the specified value, use the filter { <field>: <value> } where <value> is the element value. To specify conditions on the elements in the array field, use query operators in the query filter document: { <array field>: { <operator1>: <value1>, ... } }


1 Answers

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.

like image 130
Neil Lunn Avatar answered Nov 15 '22 03:11

Neil Lunn