Find between two string date ranges in spring mongodb

Question

I have Document Person like

{
  "_id": {
    "$oid": "5e44659dcd"
  },
  "record": {
    "Date_Of_Birth": "9/25/2018"
  },
  "_class": "com.example.entities.Birth"
}

Record is of type object, Date_Of_Birth is of type String but Date has been stored in it and I want to execute query to use $gte and $lte operations on that. I am using spring data mongodb. It seems first I have to use $dateFromString to convert it to date, But this seems to be aggregation which I don't think I require.

Below is the code which I have created so far

Pageable pageable = PageRequest.of(page, size);
Query query = new Query(
        Criteria
                .where("record.Date_Of_Birth").gte(DateOperators.dateFromString(startDate).withFormat("mm/dd/yyyy"))
                .andOperator(Criteria.where("record.Date_Of_Birth").lte(DateOperators.dateFromString(endDate).withFormat("mm/dd/yyyy"))
)).with(pageable);

which generates this query

{ "record.Date_Of_Birth" : { "$gte" : { "value" : { "dateString" : "9/25/2018", "format" : "mm/dd/yyyy"}}}, "$and" : [{ "record.Date_Of_Birth" : { "$lte" : { "value" : { "dateString" : "9/25/2018", "format" : "mm/dd/yyyy"}}}}]}

and is not working at all. Any idea?

well... I don't have choice at the moment. The json shared is just a sample. Actual json is quite different and has many other properties. My application would be receiving data and storing as it is in db. So i have to query on what I have for the moment. — iCurious
– iCurious, Commented Feb 14, 2020 at 13:54
@prasad_ it is of type string and I am passing value of "9/25/2018" — iCurious
– iCurious, Commented Feb 14, 2020 at 14:16
You are comparing a string to a date. I suspect you will need to convert the field in the document to date as well, possibly using $expr. — Joe
– Joe, Commented Feb 14, 2020 at 16:37

Joe · Accepted Answer · 2020-02-14 21:49:00Z

1

$expr permits using aggregation operators inside the query. This will permit you to convert the stored value to a date so you can do an apples to apples comparison.

db.collection.find({
  $expr: {
    $gte: [
      {$dateFromString: {
           dateString: "$record.Date_Of_Birth", 
           format: "%m/%d/%Y", 
           timeZone: "EST5EDT"}},
      {$dateFromString: {
           dateString: "9/25/2018", 
           format: "%m/%d/%Y", 
           timeZone: "EST5EDT"}}
    ]
  }
})

edited Feb 14, 2020 at 21:49

answered Feb 14, 2020 at 18:32

Joe

29k4 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

iCurious Over a year ago

it's only matching the date "9/25/2018" and not the dates which are greater than that... like "9/26/2018" , etc. what could be the issue?

Joe Over a year ago

I put the $dateFromString clauses in the wrong order. Fixed now

iCurious Over a year ago

Thanks! I have modified it to include $lte as well with $and.

prasad_ · Accepted Answer · 2020-02-15 11:58:36Z

Since the date format for the document field Date_Of_Birth and the input from-to dates are in string data and are formatted as "MM/dd/yyyy" the comparison will not be correct if used as they are. There are different ways to handle this issue.

Convert both the date formats to "yyyy/MM/dd" strings and compare or convert both dates to Date objects and compare. Note the sample code shows usage of date values converted to Date objects.

To convert the dates for the find query, the filter will use the Aggregation date or string conversion operators. The find query filter can use Aggregation operators only with the $exproperator.

I could not figure clearly to use $expr with MongoDB Spring-Data APIs (I suspect there is no such API for handling the $expr, but there may be a workaround for that).

The way to run the query clearly is to use an Aggregation. The following are the Mongo Shell and the Spring Data versions.

db.persons.aggregate( [ 
  { 
      $addFields: { 
          matches: { 
              $and: [ 
                  { $gte: [ 
                      { $dateFromString: { dateString: "$record.Date_Of_Birth", format: "%m/%d/%Y" } },  
                       ISODate("2018-09-24T00:00:00.00Z") // input from date
                   ] },
                 { $lte: [ 
                     { $dateFromString: { dateString: "$record.Date_Of_Birth", format: "%m/%d/%Y" } }, 
                      ISODate("2018-11-10T00:00:00.00Z") // input to date
                 ] }
              ]
          }
      }
  },
  { 
      $match: { matches: true } 
  }
] )

The Spring-Data Java code:

// Convert the String input dates to Date objects to be used in the query
String from = "9/24/2018";
String to = "11/10/2018";
DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
Date fromDt = dateFormat.parse(from);
Date toDt = dateFormat.parse(to);

// Build and run the query
MongoOperations mongoOps = new MongoTemplate(MongoClients.create(), "test");
Aggregation agg = newAggregation(
    project("record.Date_Of_Birth")
        .and(Gte.valueOf(DateOperators.DateFromString.fromStringOf("record.Date_Of_Birth").withFormat("%m/%d/%Y")) 
            .greaterThanEqualToValue(fromDt))
                .as("matches1")
        .and(Lte.valueOf(DateOperators.DateFromString.fromStringOf("record.Date_Of_Birth").withFormat("%m/%d/%Y")) 
            .lessThanEqualToValue(toDt))
              .as("matches2"),
    match(where("matches1").is(true)),
    match(where("matches2").is(true))
);

AggregationResults<Person> results = mongoOps.aggregate(agg, "persons", Person.class);
results.forEach(System.out::println);

Collectives™ on Stack Overflow

Find between two string date ranges in spring mongodb

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related