Compare last Value to Second Last value of an Array

Question

I'd like to write a query that returns documents from MongoDB based on a calculation of two items stored in an array on a document.

In this case I want to return all documents where the last item in an array is less than the second-to-last item in the same array.

Is this possible with MongoDB? Can you point me in the right direction RE the docs?

Any pointers greatly appreciated.

Answer 1

In modern MongoDB releases ( 3.2 and upwards ) the best operation here is to use $redact which performs a "logical filter" based on conditions along with $arrayElemAt to get the single values from the array.

Given a sample:

{ "_id": 1, "data": [1,2,3] },
{ "_id": 2, "data": [3,2,1] }

Then the query:

db.collection.aggregate([
  { "$redact": {
     "$cond": {
       "if": { 
         "$lt": [
           { "$arrayElemAt": [ "$data", -1 ] },
           { "$arrayElemAt": [ "$data", -2 ] }
         ]
       },
       "then": "$$KEEP",
       "else": "$$PRUNE"
     }
  }}
])

If the document contains "properties" in sub-documents within the array members, then you apply $map in order to just extract the values of the property you want to inspect for comparison. Also some help from $let so you don't need to repeat the expression.

As a sample:

{ 
  "_id": 1,
  "data": [
     { "name": "c", "value": 1 },
     { "name": "b", "value": 2 },
     { "name": "a", "value": 3 }
  ]
},
{ 
  "_id": 2, 
  "data": [
    { "name": "a", "value": 3 },
    { "name": "b", "value": 2 },
    { "name": "c", "value": 1 }
  ]
}

And the query:

db.collection.aggregate([
  { "$redact": {
     "$cond": {
       "if": { 
         "$let": {
           "vars": { 
             "data": { 
               "$map": {
                 "input": "$data",
                 "as": "el",
                 "in": "$$el.value"
               }
             }
           },
           "in": {
             "$lt": [
               { "$arrayElemAt": [ "$$data", -1 ] },
               { "$arrayElemAt": [ "$$data", -2 ] }
             ]
           }
         }
       },
       "then": "$$KEEP",
       "else": "$$PRUNE"
     }
  }}
])

Getting the "property" value is important here since the lexical comparison of the Object will not necessarily match the condition when compared to the other array element.

For older versions of MongoDB or as an alternate you can use $where to evaluate the condition instead:

db.collection.find(function() {
    return this.data.pop().value < this.data.pop().value
})

This does use JavaScript evaluation to determine the results, which does run slower than the native coded operators of the aggregation framework. So though the expression is simple to write, it's not the most performant way of doing so.

Whilst it is "possible" to do with the aggregation framework in earlier versions, you really should not. The process would involve taking the $last element from the array by "re-grouping" the array and then filtering out the comparison in order to get the "next" $last element. This is generally not a very good idea for performance:

db.collection.aggregate([
    // Unwind array
    { "$unwind": "$data" },

    // Group back and get $last
    { "$group": {
        "_id": "$_id",
        "data": { "$push": "$data" },
        "lastData": { "$last" "$data" }
    }},
    // Unwind again
    { "$unwind": "$data" },
    // Compare to mark the last element
    { "$project": {
        "data": 1,
        "lastData": 1,
        "seen": { "$eq": [ "$lastData", "$data" ] }
    }},
    // Filter the previous $last from the list
    { "$match": { "seen": false } },
    // Group back and compare values
    { "$group": {
        "_id": "$_id",
        "data": { "$push": "$data" },
        "lastData": { "$last": "$lastData" },
        "secondLastData": { "$last": "$data" },
        "greater": {
            "$last": { "$lt": [ "$data.value", "$lastData.value" ] } 
        }
    }},
    // Filter to return only true
    { "$match": { "greater": true } }
 ])

And that is a pretty ugly process whereas the $where is much cleaner and more performant in this case. So you would only use this in earlier MongoDB versions in a case where you needed to have "further" aggregation operations performed on the data that matches that condition.

So the compelling case here is to get a recent version and use $redact with the logical comparison in a "single" pipeline stage. Each aggregation pipeline stage adds "cost" to the overall processing time of the result, so "less is more" as always.

Answer 2

If you have MongoDB 3.2, you can use aggregation framework to fetch ids of documents matching criteria. Once you have Ids you can iterate over to process as desired.

Example:

db.collection.aggregate([
  {$project:{
    cond :{$cond:[{
      $gt:[{
        $slice:["$v", -2,1]
       }, {
        $slice:["$v", -1,1]
       }]
      }, true, false]
    }}
  },
  {$match:{cond: true}}
])

I have following documents in my collection:

{ 
    "_id" : ObjectId("57094622b08be16cf12fcf6f"), 
    "v" : [
        1.0, 
        2.0, 
        3.0, 
        4.0, 
        8.0, 
        7.0
    ]
}
{ 
    "_id" : ObjectId("5709462bb08be16cf12fcf70"), 
    "v" : [
        1.0, 
        2.0, 
        3.0, 
        4.0, 
        8.0, 
        10.0
    ]
}

According to your problem statement, you want to select document whose last element in array is less than 2nd last, so it should select document containing _id = ObjectId("57094622b08be16cf12fcf6f")

Running aggregation query will yield.

{ 
    "_id" : ObjectId("57094622b08be16cf12fcf6f"), 
    "cond" : true
}

Which is what we are hoping for.

As I mentioned above, you can iterate over returned information and can take whatever action you want including fetching full document.

Note: If your document is simple, you can project fields and there will be no need of cursor iteration to fetch full document. However, in my example I'm assuming document is complex and no upfront information of document properties or attributes is available .