MongoDB - Query on the last element of an array?

Question

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.

Refutative

What do your documents look like? – Infertile

Well the array I'm trying to test is actually nested within another array, but I don't think that should have any effect. Basically what I want to do is, in my find selector, only return documents where a particular field in the last element of an array matches a certain value. – Refutative

You should look into aggregation, then play with $unwind, $project, $match, and $group – Quadruple

With the newest MongoDB, you can do this: find({"array.-1.field":"value"}) – Sentimental

@MarsLee that doesn't work for me on version 4.2.0 - do you have any more information on that? – Accordance

Answer 1

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.

Refutative

Answer 2

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" } }
]);

Flynn

in 3.2 there's the $arrayElemAt operator, which works with negative indices. so this can instead be { $project: { id: 1, myField: { $arrayElemAt: [ "$myField", -1 ] } } }. I don't know if a slice with just one element is just as efficient – Hassi

This outputs exactly what the question wanted. – Calandracalandria

Since MongoDB v3.4, there is also $addFields operator which means you no longer have to project individual fields. – Levina

Answer 3

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);

Defunct

For embedded arrays why do you use 0 and not -1? – Wimer

This solution worked for me. I used for embedded arrays to check and see if a date inside the array itself is less than the current date. And if it's less than I update the document. const query = { $expr: { $lt: [{ $arrayElemAt: ["$lessons.studentData.dueDate", -1] }, new Date()] } }; const update = { completed: true }; Class.updateMany(query, update) – Delete

db.col.find({$expr: {$gt: [{$arrayElemAt: ["array", -1]}, value]}}) threw an error for me. --> db.col.find({$expr: {$gt: [{$arrayElemAt: ["$array", -1]}, value]}}) – Sinuation

Answer 4

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" } }
])

Binnie

Answer 5

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.

Filibeg

This just returns the last element, right? That's not what I'm trying to do. I want my find selector to only return documents where the last element of the array matches a certain value. – Refutative

Just keep in mind that '$slice' return an array, while '$arrayElemAt' return an document (same sintax). – Tallbott

Answer 6

Version 3.6 use aggregation to achieve the same.

db.getCollection('deviceTrackerHistory').aggregate([
   {
     $match:{clientId:"12"}
   },
   {
     $project:
      {
         deviceId:1,
         recent: { $arrayElemAt: [ "$history", -1 ] }
      }
   }
])

Watercraft

Answer 7

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.

Santasantacruz

I thought about doing this, but my application accesses Mongo using Spring Data, which at the time did not support pushing to the front of an array. Not sure if it does now. – Refutative

Answer 8

Not sure about performance, but this works well for me:

db.getCollection('test').find(
  {
    $where: "this.someArray[this.someArray.length - 1] === 'pattern'"
  }
)

Sickler

$where is very slow, because it runs javascript – Exteroceptor

Thank you very much! Might not be the most performant solution, but works like a charm for me. Not so slow for my data, maybe this performance difference is only perceptible within large datasets. – Renunciation

bad practice @Exteroceptor . – Ouzel

Answer 9

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.

Joust

Answer 10

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]}})

Blowing

Thank you for the editing; i tried a few times and i think i worked it out: ``` db.col.find({$expr:{$and:[{$gt: [{"$arrayElemAt": ["$array.field", -1]}, value]},{$eq:[{"$orderNumber"},"12345"]} ] }}) ``` – Blowing

Answer 11

db.collection.aggregate([
  {
    $match: { 
      $and: [ 
        { $expr: { $eq: [{ "$arrayElemAt": ["$fieldArray.name", -1] }, "value"] } }, 
        { $or: [] }
      ] 
    } 
  }
]);

Pray

Only select: { $project: {"fieldArray": { $arrayElemAt: ["$fieldArray", -1] }} } – Pray

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Treehopper