MongoDB: find all documents except those matching the date range condition

Question

Requirement: I want a collection of all clients 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 tried the following, but it always returns "Sheldon" (perhaps because another event ends the criteria?).

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

What can I do to return only documents for "Leonard" and "Raj" ?

Answer 1

To prevent users from visiting the 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 returned request contains only documents for Leonard and Raj for your example.

NB: Like your query, but I used find instead of where , no need to use $and and use $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 applying my request:

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

Answer 2

When I run a query with your exact data, the Sheldon document is returned because when you compare the string “2 hours ago” with the string “5 hours ago”, the character “2” is less than the character “5” "so the document will be correctly returned. String 10 hours ago also sorted as less than 5 hours ago.

However, if you tune the data to actual numbers as shown below, the Sheldon document is not returned.

So your query looks good to me, you just need to replace the timestamps with either Dates, numbers, or even strings that are structured so that they will be sorted in the correct order.

Modified data:

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

Modified request:

 { $and: [ { 'events': { $not: { $elemMatch: { event: "View", timestamp: { $gte: -5 } } } } } ] } 

Result:

 { "name" : "Leonard", "events" : [ { "event" : "View", "timestamp" : -10 } ] } { "name" : "Raj", "events" : [ { "event" : "Some other event", "timestamp" : -6 } ] }