Filtering Date Values Using $lt and $gt in MongoDB

Question

In my MongoDB/Node backend I am setting up a function that takes user-inputted filter values and filters the data accordingly via a GET request. This is working for the most part. Most of filters return records as expected.

However, filtering on date of birth (dob) is proving problematic. Specifically, when taking in values for both dobBefore and dobAfter, the results are incorrect. I don't get the date range returned that I'd expect based on the records I see.

For my filter code, if there is an inputted value I first convert it to a date (to match against our database values -- which are dates), and then I run a search to return only records where the dob is, in the case of dobBefore -- a date prior to that date value inputted (using the $lt operator), and, in the case of dobAfter -- a date after the date value inputted (using the $gt operator). This is the code in question:

  // dobBefore filter
  if (dobBefore) {
    let dobBeforeDate = new Date(dobBefore);
    console.log('dobBeforeDate: ', dobBeforeDate);
        search['dob'] = { $lt: dobBeforeDate };
  }

  // dobAfter filter
  if (dobAfter) {
    let dobAfterDate = new Date(dobAfter);
    console.log('dobAfterDate: ', dobAfterDate);
        search['dob'] = { $gt: dobAfterDate };
  }

The full GET request looks something like this:

api.somesite.com/v0/customers/details?dobBefore=1998-12-06&dobAfter=1998-02-06&apikey=1d1d1d1&token=ffb4bbb3

As I said earlier, while I should only see records where the dob is before 1998-12-06, and after 1998-02-06 (so within that 10 month range), I don't see that reflected in the records returned. For instance, this is one of the records that returns from the query above (clearly well beyond the targeted range):

"dob": "2007-08-23T05:00:00.000Z"

To clarify, in the document, dob is store like this, and is of type "date":

1998-12-01T06:00:00.000Z

Is there some gotcha here with dates and the $lt and $gt operators? Or is there something else I'm missing?

EDIT/UPDATE:

After some feedback from @Veeram, I realize I am overriding one query with the other. I need to combine them. I'm thinking something like this should work (by the way, I initialize dobBefore and dobAfter as empty strings if there is no value, that's why I'm checking for empty strings):

let search = {};

// dobBefore filter
if (dobBefore && dobAfter === '') {
   let dobBeforeDate = new Date(dobBefore);
   search['dob'] = { $lt: dobBeforeDate };
}

// dobAfter filter
if (dobAfter && dobBefore === '') {
   let dobAfterDate = new Date(dobAfter);
   search['dob'] = { $gt: dobAfterDate };
}

// filter both at once
if (dobBefore && dobAfter) {
   let dobBeforeDate = new Date(dobBefore);
   let dobAfterDate = new Date(dobAfter);
   search['dob'] = { "$lt" : dobBeforeDate , "$gt" : dobAfterDate };
}

EDIT/UPDATE 2: The code just above here works as intended. Problem solved!

You may need to change your date format. Please see https://stackoverflow.com/questions/2943222/find-objects-between-two-dates-mongodb — ecg8, Mar 16 '18 at 19:19
how do you store dates in document? I'd recommend to attach few sample documents. — Saleem, Mar 16 '18 at 19:26
In the document I store dates like this: "1998-12-01T06:00:00.000Z". And the type is "date". — Muirik, Mar 16 '18 at 19:50

score 1 · Accepted Answer · answered Mar 16 '18 at 19:37

1

You are overwriting the dob key with $gt value.

You want

{"dob" :{
   "$lt" : ISODate("2018-03-16T19:31:01.229Z"),
   "$gt" : ISODate("2018-03-25T19:31:01.279Z")
}}

Try

let search = {dob:{}};
if (dobBefore) {
    let dobBeforeDate = new Date(dobBefore);
    console.log('dobBeforeDate: ', dobBeforeDate);
    search['dob'].$lt =  dobBeforeDate;
  }

  // dobAfter filter
if (dobAfter) {
    let dobAfterDate = new Date(dobAfter);
    console.log('dobAfterDate: ', dobAfterDate);
    search['dob'].$gt=  dobAfterDate ;
}

collection.find(search)

answered Mar 16 '18 at 19:37

s7vr

73,656
11
106
127

I'm not sure what the issue is you're pointing to. For this code: search['dob'] = { $gt: dobAfterDate }; -- that is effectively checking the 'dob' field and returning records where the dob is later/greater than the inputted date value stored in dobAfterDate varaible. Right? Perhaps the issue is that I need to convert to am ISODate. – Muirik Mar 16 '18 at 19:49
I was talking about the bug your code has in case where you want to do range search ie dob field being between dateBefore and dateAfter your posted code will not work. Your code will only work if you use either $lt or $gt. – s7vr Mar 16 '18 at 19:52
What do you get when you perform $lt or $gt search ? Are your results missing expected documents ? – s7vr Mar 16 '18 at 19:56
So your range query doesn't work right ? If yes, that is exactly what will happen with posted code. You do first `"dob" :{ "$lt" : date1}` and later you overwrite it with `"dob" :{ "$gt" : date2}`. So the query that get sent to server is `"dob" :{ "$gt" : date2} ` – s7vr Mar 16 '18 at 20:03
Okay, I see what you're saying. I need to somehow combine the query, and query it at one time, rather than in two separate requests. – Muirik Mar 16 '18 at 20:04
Yes you can use the posted answer which works for both range queries and $lt, $gt queries. – s7vr Mar 16 '18 at 20:05
Seems like the general right approach, but I'll need to tweak your code a bit, because right I'm getting an "TypeError: Cannot set property '$lt' of undefined" error. – Muirik Mar 16 '18 at 20:12
Yes, I'm not sure how your search variable is initialized. `let search = {dob:{}};` works for me. – s7vr Mar 16 '18 at 20:13
Another way to do it would be to make one query with both a $gt and $lt, right? I assume that's also do-able with Mongo - something like: $lt: dobBefore && $gt: dobAfter – Muirik Mar 16 '18 at 20:18
Yes it is possible to do it in mongo (`{"dob" :{ "$lt" : dobBefore , "$gt" : dobAfter }}`) but the way you are approaching this is correct i.e. dynamically building the query string. I can help you if you show me how your `search` variable is set up. – s7vr Mar 16 '18 at 20:21
Search is initialized like this: let search = {}; – Muirik Mar 16 '18 at 20:28
Something like what I added above? – Muirik Mar 16 '18 at 20:39
Can you try let search = {dob:{}}; ? and use the query building logic from my answe. – s7vr Mar 16 '18 at 20:41
When I tried that I got errors. What do you think about what I just added above? – Muirik Mar 16 '18 at 20:42

Filtering Date Values Using $lt and $gt in MongoDB

1 Answers1