MongoDB - Query on the last element of an array?
ArraysMongodbMongooseMongodb QuerySpring Data-MongodbArrays Problem Overview
I know that MongoDB supports the syntax find{array.0.field:"value"}
, but I specifically want to do this for the last element in the array, which means I don't know the index. Is there some kind of operator for this, or am I out of luck?
EDIT: To clarify, I want find() to only return documents where a field in the last element of an array matches a specific value.
Arrays Solutions
Solution 1 - Arrays
In 3.2 this is possible. First project so that myField contains only the last element, and then match on myField.
db.collection.aggregate([
{ $project: { id: 1, myField: { $slice: [ "$myField", -1 ] } } },
{ $match: { myField: "myValue" } }
]);
Solution 2 - Arrays
You can use $expr ( 3.6 mongo version operator ) to use aggregation functions in regular query.
Compare query operators
vs aggregation comparison operators
.
For scalar arrays
db.col.find({$expr: {$gt: [{$arrayElemAt: ["$array", -1]}, value]}})
For embedded arrays - Use $arrayElemAt
expression with dot notation to project last element.
db.col.find({$expr: {$gt: [{"$arrayElemAt": ["$array.field", -1]}, value]}})
Spring @Query
code
@Query("{$expr:{$gt:[{$arrayElemAt:[\"$array\", -1]}, ?0]}}")
ReturnType MethodName(ArgType arg);
Solution 3 - Arrays
Starting Mongo 4.4
, the aggregation operator $last
can be used to access the last element of an array:
For instance, within a find
query:
// { "myArray": ["A", "B", "C"] }
// { "myArray": ["D"] }
db.collection.find({ $expr: { $eq: [{ $last: "$myArray" }, "C"] } })
// { "myArray": ["A", "B", "C"] }
Or within an aggregation
query:
db.collection.aggregate([
{ $addFields: { last: { $last: "$myArray" } } },
{ $match: { last: "C" } }
])
Solution 4 - Arrays
use $slice.
db.collection.find( {}, { array_field: { $slice: -1 } } )
Editing:
You can make use of
{ <field>: { $elemMatch: { <query1>, <query2>, ... } } }
to find a match.
But it won't give exactly what you are looking for. I don't think that is possible in mongoDB yet.
Solution 5 - Arrays
I posted on the official Mongo Google group here, and got an answer from their staff. It appears that what I'm looking for isn't possible. I'm going to just use a different schema approach.
Solution 6 - Arrays
Version 3.6 use aggregation to achieve the same.
db.getCollection('deviceTrackerHistory').aggregate([
{
$match:{clientId:"12"}
},
{
$project:
{
deviceId:1,
recent: { $arrayElemAt: [ "$history", -1 ] }
}
}
])
Solution 7 - Arrays
You could use $position: 0
whenever you $push
, and then always query array.0
to get the most recently added element. Of course then, you wont be able to get the new "last" element.
Solution 8 - Arrays
Not sure about performance, but this works well for me:
db.getCollection('test').find(
{
$where: "this.someArray[this.someArray.length - 1] === 'pattern'"
}
)
Solution 9 - Arrays
You can solve this using aggregation.
model.aggregate([
{
$addFields: {
lastArrayElement: {
$slice: ["$array", -1],
},
},
},
{
$match: {
"lastArrayElement.field": value,
},
},
]);
Quick explanations. aggregate
creates a pipeline of actions, executed sequentially, which is why it takes an array as parameter. First we use the $addFields
pipeline stage. This is new in version 3.4, and basically means: Keep all the existing fields of the document, but also add the following. In our case we're adding lastArrayElement
and defining it as the last element in the array called array
. Next we perform a $match
pipeline stage. The input to this is the output from the previous stage, which includes our new lastArrayElement
field. Here we're saying that we only include documents where its field field
has the value value
.
Note that the resulting matching documents will include lastArrayElement
. If for some reason you really don't want this, you could add a $project
pipeline stage after $match
to remove it.
Solution 10 - Arrays
For the answer use $arrayElemAt
,if i want orderNumber:"12345" and the last element's value $gt than "value"? how to make the $expr? thanks!
For embedded arrays - Use $arrayElemAt
expression with dot notation to project last element.
db.col.find({$expr: {$gt: [{"$arrayElemAt": ["$array.field", -1]}, value]}})