0

So I have a collections which stores the start and end times for an event and I would like a user to be able to query for events by specifying "filter" start and end times. The query should return any events that intersect with the filter times. I thought I was building the query correctly, but it's not returning the results I want.

Example data (simplified mongo document to get the idea, times are HHMMSS):

{ ..., "start" : "050520", "end" : "051309" } //Should match
{ ..., "start" : "051125", "end" : "051925" } //Should match
{ ..., "start" : "052049", "end" : "052420" } //Should match
{ ..., "start" : "050000", "end" : "050500" }
{ ..., "start" : "052100", "end" : "052721" }

The query I tried was (Forgive me if I missed a bracket here, that's not the issue):

find( { $or : [ 
            { start : { $gte : "050600", $lt : "052100" } }, 
            { end : { $lt : "052100", $gt : "050600" } } 
        ] } )

Which only returned one of the three results. Switching the order of the $or parts ( querying against the end times before the start times) gives me two results but not all three. I seem to remember reading that for compound queries mongo throws away results that don't match the first part, before applying the second part of the query, but I thought the $or operator would handle that. Any ideas? Is my query incorrect, is this a limitation of querying in mongo?

mike
  • 3,339
  • 6
  • 30
  • 34

2 Answers2

4

Using MongoDB 2.4.5 I tried the above and got back in both cases the first 2 of your "Should match" results.

I don't believe the 3rd should be returned as it would require ("052049" >= "050600" && "052049" < "052000") or ("052420" < "052000" && "052420" > "050600") neither of which evaluate to true.

MongoDB shell version: 2.4.5
connecting to: test
> 
> db.mycol.save({ "start" : "050520", "end" : "051309" }) 
> db.mycol.save({ "start" : "051125", "end" : "051925" })
> db.mycol.save({ "start" : "052049", "end" : "052420" }) 
> db.mycol.save({ "start" : "050000", "end" : "050500" })
> db.mycol.save({ "start" : "052100", "end" : "052721" })
>
> db.mycol.find( 
... { $or : 
... [ { $and : [ { start : { $gte : "050600" } }, { start : { $lt : "052000" } } ] }, 
...   { $and : [ { end : { $lt : "052000" } }, { end : { $gt : "050600" } } ] } 
... ] 
...     } )
{ "_id" : ObjectId("520528522683a40682f12b5c"), "start" : "050520", "end" : "051309" }
{ "_id" : ObjectId("520528522683a40682f12b5d"), "start" : "051125", "end" : "051925" }
> 
> 
> db.mycol.find( 
... { $or : 
... [     { $and : [ { end : { $lt : "052000" } }, { end : { $gt : "050600" } } ] } , 
...   { $and : [ { start : { $gte : "050600" } }, { start : { $lt : "052000" } } ] }
... ] 
...     } )
{ "_id" : ObjectId("520528522683a40682f12b5c"), "start" : "050520", "end" : "051309" }
{ "_id" : ObjectId("520528522683a40682f12b5d"), "start" : "051125", "end" : "051925" }
>
James Wahlin
  • 2,811
  • 21
  • 22
  • Hah you are right the third one shouldn't match. Hmm I'm wondering, I'm currently using MongoDB 2.2.2 which may be the problem. I'll try upgrading and verify. – mike Aug 09 '13 at 18:04
  • The behavior for this should be identical between 2.2.2 and 2.4.5. Let me know if you see different. – James Wahlin Aug 09 '13 at 18:23
  • Well I upgraded to 2.4.5 and it worked... So I went back to 2.2.2 and tried again and it worked as expected there too. I must have had a typo :/ Sorry! – mike Aug 12 '13 at 17:39
1

Your query should work fine, but the third one shouldn't match as both start and end are out of the range.

However, you can write your query more clearly and succinctly as:

db.test.find({ $or: [
    { start: { $gte: "050600", $lt : "052000" }}, 
    { end: { $lt: "052000", $gt: "050600" }}
]});
JohnnyHK
  • 305,182
  • 66
  • 621
  • 471
  • Doh! I wanted my filter to encompass that end time, I missed in the example. But this is good to know I didn't realize I could join the gte, lt operators into one statement. I think the query may not be working because I am using an out of date version of mongo. – mike Aug 09 '13 at 18:05
  • @mike I doubt it as I don't think any of this behavior has changed in recent versions. – JohnnyHK Aug 09 '13 at 18:10