CODEWITHSUNDEEP
javascriptnode.jsmongodbdate
Muirik
Muirik

Reputation: 6289

Filtering Date Values Using $lt and $gt in MongoDB

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!

Upvotes: 0

Views: 9354

Answers (1)

s7vr
s7vr

Reputation: 75934

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)

Upvotes: 1

Related Questions