MongoDB: Find all documents except those where a date range condition matches

Question

Requirement: I want a collection of all customers who did not perform the "View" event in last 5 hours.

Data:

{
    name: "Sheldon",
    events: [
        {
            event: "View",
            timestamp: "timestamp equivalent to 10 hours ago"
        },
        {
            event: "Some other event",
            timestamp: "timestamp equivalent to 8 hours ago"
        },
        {
            event: "View",
            timestamp: "timestamp equivalent to 2 hours ago"
        }
    ]
},
{
    name: "Leonard",
    events: [
        {
            event: "View",
            timestamp: "timestamp equivalent to 10 hours ago"
        }
    ]
},
{
    name: "Howard",
    events: [
        {
            event: "View",
            timestamp: "timestamp equivalent to 2 hours ago"
        }
    ]
},
{
    name: "Raj",
    events: [
        {
            event: "Some other event",
            timestamp: "timestamp equivalent to 6 hours ago"
        }
    ]
}

I have tried out the following, but it always returns "Sheldon" (possibly because the other event ends up satisfying the criteria?).

q.where({ 
    $and: [
        {
            'events': { 
                $not: { 
                    $elemMatch: {
                        event: "View",
                        timestamp: {
                            $gte: "timestamp equivalent to 5 hours ago"
                        }
                    }
                }
            }
        }
    ]
});

What can I do so that only documents for "Leonard" and "Raj" are returned?

Check [this question](http://stackoverflow.com/questions/19465634/mongodb-query-on-nested-array-elements) once. — Tanmay Baranwal, Mar 29 '17 at 09:22
you can check out (https://docs.mongodb.com/manual/reference/operator/query-comparison/) for more details. — Shumi Gupta, Mar 29 '17 at 09:33
@TanmayBaranwal my example query is equivalent to the one in your link, does not work as expected because there's another event that satisfies the criteria. — hiteshspac, Mar 29 '17 at 09:37
Do you have dates in ISO format ? Why not update your post with actual dates ? It will make it easy for people trying to answer and test your code. — s7vr, Mar 29 '17 at 10:38

Shaishab Roy · Accepted Answer · 2017-03-29T11:53:20.560

To get users how has not visited in last 5 hours. you can try it

db.collectionName.find({
    events: { 
        $not: { 
            $elemMatch: {
                event: "View",
                timestamp: {
                    $gt: ISODate("2017-03-29T05:12:37.420Z") // equivalent to 5 hours ago
                }
            }
         }
    }
}, {name: 1})

This query returned only documents for Leonard and Raj for your given example

N.B: Like your query but I used find instead of where, no need to use $and and used $gt instead of $gte

Tested for documents.

{
    "_id" : ObjectId("58db886e4b9e731aaefa9820"),
    "name" : "Sheldon",
    "events" : [ 
        {
            "event" : "View",
            "timestamp" : ISODate("2017-03-29T00:09:18.723Z")
        }, 
        {
            "event" : "Some other event",
            "timestamp" : ISODate("2017-03-29T02:10:04.492Z")
        }, 
        {
            "event" : "View",
            "timestamp" : ISODate("2017-03-29T08:11:02.196Z")
        }
    ]
}
{
    "_id" : ObjectId("58db886e4b9e731aaefa9821"),
    "name" : "Leonard",
    "events" : [ 
        {
            "event" : "View",
            "timestamp" : ISODate("2017-03-29T00:11:23.084Z")
        }
    ]
}
{
    "_id" : ObjectId("58db886e4b9e731aaefa9822"),
    "name" : "Howard",
    "events" : [ 
        {
            "event" : "View",
            "timestamp" : ISODate("2017-03-29T08:11:02.196Z")
        }
    ]
}
{
    "_id" : ObjectId("58db886e4b9e731aaefa9823"),
    "name" : "Raj",
    "events" : [ 
        {
            "event" : "Some other event",
            "timestamp" : ISODate("2017-03-29T04:10:42.972Z")
        }
    ]
}

After apply my query result:

{
    "_id" : ObjectId("58db886e4b9e731aaefa9821"),
    "name" : "Leonard"
},
{
    "_id" : ObjectId("58db886e4b9e731aaefa9823"),
    "name" : "Raj"
}

How is this different from the one I have mentioned in the question? — hiteshspac, Mar 29 '17 at 10:26
No need to use `$and` and used `$gt` instead of `$gte` other than your query should be working. Tested using your data :) @hiteshspac — Shaishab Roy, Mar 29 '17 at 10:29
@ShaishabRoy The query u have written does not gives the output as desired. Its retutning the whole dataset. Can you please check! — Rohit Rai, Mar 29 '17 at 11:13
I asume `timestamp` is `Date type` so if `timestemp` is `Date type` then it should be work. I tested @RohitRai — Shaishab Roy, Mar 29 '17 at 11:45

MongoDB: Find all documents except those where a date range condition matches

1 Answers1