CODEWITHSUNDEEP
javaspringmongodbspring-bootaggregation-framework
iCurious
iCurious

Reputation: 8313

Find between two string date ranges in spring mongodb

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?

Upvotes: 2

Views: 6090

Answers (2)

prasad_
prasad_

Reputation: 14317

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);

Upvotes: 0

Joe
Joe

Reputation: 28356

$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"}}
    ]
  }
})

Upvotes: 1

Related Questions