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
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.
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.
You can't do this in one go with this schema design. You can either store the length and do two queries, or store the last comment additionally in another field:
{
'_id': 'foo';
'comments' [
{ 'value': 'comment #1', 'by': 'Ford' },
{ 'value': 'comment #2', 'by': 'Arthur' },
{ 'value': 'comment #3', 'by': 'Zaphod' }
],
'last_comment': {
'value': 'comment #3', 'by': 'Zaphod'
}
}
Sure, you'll be duplicating some data, but atleast you can set this data with $set together with the $push for the comment.
$comment = array(
'value' => 'comment #3',
'by' => 'Zaphod',
);
$collection->update(
array( '_id' => 'foo' ),
array(
'$set' => array( 'last_comment' => $comment ),
'$push' => array( 'comments' => $comment )
)
);
Finding the last one is easy now!
You could do this with a $where operator:
db.example.find({ $where:
'this.comments.length && this.comments[this.comments.length-1].by === "Abe"'
})
The usual slow performance caveats for $where apply. However, you can help with this by including "comments.by": "Abe" in your query:
db.example.find({
"comments.by": "Abe",
$where: 'this.comments.length && this.comments[this.comments.length-1].by === "Abe"'
})
This way, the $where only needs to be evaluated against documents that include comments by Abe and the new term would be able to use an index on "comments.by".
I'm just doing :
db.products.find({'statusHistory.status':'AVAILABLE'},{'statusHistory': {$slice: -1}})
This gets me products for which the last statusHistory item in the array, contains the property status='AVAILABLE' .
I am not sure why my answer above is deleted. I am reposting it. I am pretty sure without changing the schema, you should be able to do it this way.
db.example.find({ "comments:{$slice:-1}.by" : "Abe" }
// ... or
db.example.find({ "comments.by" : "Abe" }
This by default takes the last element in the array.
