CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
moogeek
moogeek

Reputation: 437

Match docs having all array element within $gte $lte

Here are my example documents:

{
updated: [
    1461062102,
    1461062316
],
name: "test1",
etc: "etc"
}

{
updated: [
    1460965492,
    1461060275
],
name: "test2",
etc: "etc"
}


{
updated: [
    1461084505
],
name: "test3",
etc: "etc"
}

{
updated: [
    1461060430
],
name: "test4",
etc: "etc"
}

{
updated: [
    1460965715,
    1461060998
],
name: "test5",
etc: "etc"
}

What is the correct usage of find query to fetch all documents matching updated date within $gte and $lte criteria?

for example

db.test.find({'updated':{$elemMatch:{$gte:1461013201,$lte:1461099599}}})

I can use $or and set it it like updated.0:{$gte:1461013201,$lte:1461099599}, update.1:{$gte:1461013201,$lte:1461099599} etc but what if my array will contain more updated dates?

As I understand $elemMatch doesnt' fit my criteria because it only matches the first occurence in array.

Upvotes: 4

Views: 2708

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151112

Good question. You were on the right track with $elemMatch, but this does take other logic not covered in standard operators.

So you either do with $redact:

db.test.aggregate([
  { "$match": {
    'updated': { '$elemMatch':{ '$gte':1461013201, '$lte':1461099599 } }
  }},
  { "$redact": {
    "$cond": {
      "if": { 
        "$allElementsTrue": {
          "$map": {
            "input": "$updated",
            "as": "upd",
            "in": {
              "$and": [
                { "$gte": [ "$$upd", 1461013201 ] },
                { "$lte": [ "$$upd", 1461099599 ] }
              ]
            }
          }
        }
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }}
])

Or in versions earlier than MongoDB 2.6, you handle with a $where clause:

db.test.find({
  'updated': { '$elemMatch':{ '$gte':1461013201, '$lte':1461099599 } },
  "$where": function() {
    return this.updated.filter(function(el) { 
      return el >= 1461013201 &&  el <= 1461099599;
    }).length == this.updated.length;
  }
})

The catch is that though a general native "query" operator can tell you that one array member meets the conditions, it cannot tell you that all of them do.

So the condition can either be tested with $map and $allElementsTrue, which are both available from MongoDB 2.6. With MongoDB 3.2 there is $filter and $size which are equivalent to the below JavaScript test.

Or alternately you use the JavaScript evaluation of $where to test the "filtered" array length against the original and see that they are still the same.

That's the additional logic to build in to see that all match the range conditions supplied. The aggregate method is native code as opposed to JavaScript interpretation. It runs much faster by comparison.

But you still want to keep that $elemMatch in all cases.

And of course, here are the matching documents:

{
        "updated" : [
                1461062102,
                1461062316
        ],
        "name" : "test1",
        "etc" : "etc"
}
{
        "updated" : [
                1461084505
        ],
        "name" : "test3",
        "etc" : "etc"
}
{
        "updated" : [
                1461060430
        ],
        "name" : "test4",
        "etc" : "etc"
}

Upvotes: 4

Related Questions