MongoDB: Find all documents except those where a date range condition matches
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?
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
NB: 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"
}
When I run your query with your exact data, the Sheldon document gets returned because when you compare the string "2 hours ago" to the string "5 hours ago", the character "2" is less than the character "5" so the document gets correctly returned. The string "10 hours ago" is also sorted as being less than "5 hours ago".
However if you adjust the data to have actual numbers like 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 sort 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 Query:
{
$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 } ] }
上一篇: Git rebase递归分支