CODEWITHSUNDEEP
arraysnode.jsmongodbmongoosemongodb-query
toonvanstrijp
toonvanstrijp

Reputation: 417

Query where all array items are greater than a specified condition

I have a model which contains an array with dates. I'm using a $gte operator as a condition to query the collection where all the elements in the array of dates are $gte a given date.

For example I have this document:

{ dates: [
    ISODate("2016-10-24T22:00:00.000+0000"), 
    ISODate("2017-01-16T23:00:00.000+0000")]
}

When I run this query {dates: {$gte: new Date()}}, it gives me the whole document as a result. But I want a result where every single array item matches my query, not just one.

Upvotes: 3

Views: 2333

Answers (2)

chridam
chridam

Reputation: 103435

You can also use the aggregation framework with the $redact pipeline operator that allows you to proccess the logical condition with the $cond operator and uses the special operations $$KEEP to "keep" the document where the logical condition is true or $$PRUNE to "remove" the document where the condition was false.

This operation is similar to having a $project pipeline that selects the fields in the collection and creates a new field that holds the result from the logical condition query and then a subsequent $match, except that $redact uses a single pipeline stage which is more efficient.

As for the logical condition, there are Set Operators that you can use since they allow expression that perform set operations on arrays, treating arrays as sets. These couple of these operators namely the $allElementTrue and $map operators can be used as the logical condition expression as they work in such a way that if all of the elements in the array actually are $gte a specified date, then this is a true match and the document is "kept". Otherwise it is "pruned" and discarded.

Consider the following examples which demonstrate the above concept:

Populate Test Collection

db.test.insert([
    { dates: [
        ISODate("2016-10-24T22:00:00.000+0000"), 
        ISODate("2017-01-16T23:00:00.000+0000")]
    } ,
    { dates: [
        ISODate("2017-01-03T22:00:00.000+0000"), 
        ISODate("2017-01-16T23:00:00.000+0000")]
    } 
])

$redact with $setEquals

db.test.aggregate([
    { "$match": { "dates": { "$gte": new Date() } } },
    {
        "$redact": {
            "$cond": [
                { 
                    "$allElementsTrue": {
                        "$map": {
                            "input": "$dates",
                            "as": "date",
                            "in": { "$gte": [ "$$date", new Date() ] }
                        }
                    } 
                },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

Sample Output

{
    "_id" : ObjectId("581899dda450d81cb7d87d3a"),
    "dates" : [ 
        ISODate("2017-01-03T22:00:00.000Z"), 
        ISODate("2017-01-16T23:00:00.000Z")
    ]
}

Another not-so elegant approach would be to use $where (as a last resort) with the Array.prototype.every() method:

db.test.find({ "$where": function(){ 
    return this.dates.every(function(date) {
        return date >= new Date();
    })}
})

Upvotes: 3

JohnnyHK
JohnnyHK

Reputation: 312045

You can do this by using $not and the inversion of your comparison condition:

db.test.find({dates: {$not: {$lt: new Date()}}})

So this matches docs where it's not the case that there's a dates element with a value less than the current time; in other words, all dates values are >= the current time.

Upvotes: 5

Related Questions