Querying Dates using MongoTemplate and aggregations results in a broken query

Context:

  • Spring Boot (starter): 2.7.5
  • Spring Data MongoDB (starter): 2.7
  • Java version: 17

TLDR

I’d like to generate an aggregation step like this (tested with MongoCompass and works):

{ // "$match" omitted
  "value.timestampWithoutTimezoneValue.value" : {
    $gt : ISODate('Sat Jan 14 11:29:29 CET 2023')
  }
}

but this is generated when I use MongoTemplate instead:

{
   "$match":{
      "value.timestampWithoutTimezonevalue.value":{
         "$gt":{
            "$date":"2023-01-14T10:29:29.499Z"
         }
      }
   }
}

I can’t really find the reason for this and I get zero results.

N.B.: I know that this is not the Java MongoDB library but rather something working on top of it, but I still believe I might get good feedback or ideas here.

Debug details

This is where I generate the Criteria:

    case GREATER_THAN -> {
        // LocalDateTime: 2023-01-14T11:29:29.499999999
        Date out = Date.from(((LocalDateTime) filter.getValue()).atZone(ZoneId.of("UTC")).toInstant()); // out = Sat Jan 14 12:29:29 CET 2023 (I assume it's +0)
        yield Criteria.where("value.timestampWithoutTimezonevalue.value").gt(out); // See below
    }

Criteria where

EDIT: I noticed that the conversion to Date caused a +1h instead of a +0h, but it’s not relevant for this thread’s sake. Keep reading and you’ll understand.

This is the aggregation pipeline that I submit to MongoTemplate (I omitted the rest because the rest works as intended):

{
   ...
   "pipeline":[
      ...
      {
         "$addFields":{
            "value.timestampWithoutTimezoneValue.value":{
               "$toDate":"$value.timestampWithoutTimezoneValue.value"
            }
         }
      },
      {
         "$match":{
            "value.timestampWithoutTimezonevalue.value":{
               "$gt":{
                  "$date":"2023-01-14T11:29:29.499Z"
               }
            }
         }
      }
	  ...
   ],
   ...
}

Note that:

  • Dates are stored in documents as Strings, e.g. “2023-01-07T12:30:30.500”, therefore
  • I need to convert the string to a Date object before $matching
  • (Note that I’d rather work with Date objects because I might need to run more complex queries in future)

For completion’s sake I share the small testing dataset I’m using (only the relevant field):

[{
  "value": {
    "timestampWithoutTimezoneValue": {
      "value": "2023-01-07T12:30:30.500"
    }
  }
},{
  "value": {
    "timestampWithoutTimezoneValue": {
      "value": "2023-01-08T12:30:30.500"
    }
  }
},{
  "value": {
    "timestampWithoutTimezoneValue": {
      "value": "2023-01-09T12:30:30.500"
    }
  }
},{
  "value": {
    "timestampWithoutTimezoneValue": {
      "value": "2023-01-10T12:30:30.500"
    }
  }
},{
  "value": {
    "timestampWithoutTimezoneValue": {
      "value": "2023-01-11T12:30:30.500"
    }
  }
},{
  "value": {
    "timestampWithoutTimezoneValue": {
      "value": "2023-01-12T12:30:30.500"
    }
  }
},{
  "value": {
    "timestampWithoutTimezoneValue": {
      "value": "2023-01-13T12:30:30.500"
    }
  }
},{
  "value": {
    "timestampWithoutTimezoneValue": {
      "value": "2023-01-14T12:30:30.500"
    }
  }
},{
  "value": {
    "timestampWithoutTimezoneValue": {
      "value": "2023-01-15T12:30:30.500"
    }
  }
},{
  "value": {
    "timestampWithoutTimezoneValue": {
      "value": "2023-01-16T12:30:30.500"
    }
  }
}]

Expectation of the query result:

Return documents with dates:

  • “2023-01-14T12:30:30.500” (this should be discarded considering my +1h bug)
  • “2023-01-15T12:30:30.500”
  • “2023-01-16T12:30:30.500”

Actual Result: Zero results

Conclusions:

This is one of many attempts by the way… I tried to:

  • Querying by string without converting to Date
  • Converting string dates to Longs ($toLong) instead of Date objects and querying by timestamp

None of the above worked and my impression is that my code is ok. The problem I’m facing is that my aggregation pipeline isn’t interpreted the way I expect to (structure-wise) and I can’t find a way to make it work out of MongoCompass.

Hello Capitano,

Thank you for the detailed problem description.

I would like to clear the confusion about $date as this is not a filter operator but rather a MongoDB extended JSON representation.

While date objects return to the driver with the representation of {"$date":"2022-12-21T23:00:00.000+0000"}, you cannot use it in a find operation or in an aggregation’s $match stage.

In fact, if you tried to filter in a find operation using $date , you will notice it’s not a valid operator:

MongoDB Enterprise myReplSet:PRIMARY> db.tst.find({"created_at"  : { "$date" : "2022-11-21T23:00:00.000+0000" }  } )
Error: error: {
	"operationTime" : Timestamp(1673260574, 1),
	"ok" : 0,
	"errmsg" : "unknown operator: $date",
	"code" : 2,
	"codeName" : "BadValue",
	"$clusterTime" : {
		"clusterTime" : Timestamp(1673260574, 1),
		"signature" : {
			"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
			"keyId" : NumberLong(0)
		}
	}
}

In order to pass the date correctly you should pass it as a date object, from the mongo shell I can use the function Date()

db.tst.aggregate([
...    {
...      $match: {
...      "account_id": 10, 
...      created_at:{
...      $lte:new Date("2023-01-14T11:29:29.499Z")
...      }
...      }
...    }])
{ "_id" : ObjectId("63a4645c660455aea91e63db"), "account_id" : 10, "created_at" : ISODate("2022-11-20T00:00:00Z") }
{ "_id" : ObjectId("63a46461660455aea91e63dc"), "account_id" : 10, "created_at" : ISODate("2022-11-21T00:00:00Z") }
{ "_id" : ObjectId("63a46464660455aea91e63dd"), "account_id" : 10, "created_at" : ISODate("2022-11-22T00:00:00Z") }
{ "_id" : ObjectId("63a46469660455aea91e63de"), "account_id" : 10, "created_at" : ISODate("2022-12-21T00:00:00Z") }
{ "_id" : ObjectId("63a4646d660455aea91e63df"), "account_id" : 10, "created_at" : ISODate("2022-12-22T00:00:00Z") }
{ "_id" : ObjectId("63a46471660455aea91e63e0"), "account_id" : 10, "created_at" : ISODate("2022-12-20T00:00:00Z") }

Similarly, from the driver you are using you should pass a date object in your code.

In Java, it should be something like the below:

// Define the date range
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date startDate = sdf.parse("2022-01-01");
Date endDate = sdf.parse("2022-06-30");

// Find documents within the date range
Iterable<Document> documents = collection.find(Filters.and(Filters.gte("date", startDate), Filters.lt("date", endDate)));

I hope you find the above helpful.

Regards,
Mohamed Elshafey

Hi Mohamed,

Thanks for the comment.

This thing of the MongoDB extended JSON looks a bit confusing, but regardless I tried to change my code to comply with your example:

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date endDate = sdf.parse("2023-01-14");
        aggregations.add(
            match(
                Criteria.where("value.timestampWithoutTimezonevalue.value").gt(endDate)
            )
        );

I still get no results out of the aggregation. And if I remove this $match, and therefore there are no matching conditions, then I get all the documents. That’s why I’m saying that something stinky is happening here…

Hello Capitano,

I tried to reproduce the example in my testing environment and I see it’s working as expected.

Here is the snippet that inserts 4 records, I executed an aggregation that filters on the date field:

MongoClient mongoClient = MongoClients.create("mongodb://localhost:27017");
MongoTemplate mongoTemplate = new MongoTemplate(new SimpleMongoClientDbFactory(mongoClient, "testdb"));
SampleDocument doc1 = new SampleDocument("John Doe", new SimpleDateFormat("yyyy-MM-dd").parse("2022-01-01"), 30);
SampleDocument doc2 = new SampleDocument("Jane Smith", new SimpleDateFormat("yyyy-MM-dd").parse("2022-03-01"), 25);
SampleDocument doc3 = new SampleDocument("Bob Lee", new SimpleDateFormat("yyyy-MM-dd").parse("2022-05-01"), 35);
SampleDocument doc4 = new SampleDocument("Amy Williams", new SimpleDateFormat("yyyy-MM-dd").parse("2022-07-01"), 40);
mongoTemplate.insert(doc1);
mongoTemplate.insert(doc2);
mongoTemplate.insert(doc3);
mongoTemplate.insert(doc4);
// Specify the start and end dates for the range
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
Date startDate = formatter.parse("2022-05-01");
// Create the aggregation pipeline
MatchOperation match = Aggregation.match(new Criteria("date").gte(startDate));
ProjectionOperation projectStage = Aggregation.project("name","age", "date");
Aggregation aggregation = Aggregation.newAggregation(match,projectStage); 
List<SampleDocument> result = mongoTemplate.aggregate(aggregation, "sampleDocument", SampleDocument.class).getMappedResults();
result.forEach(System.out::println);
System.exit(0);

And here is the output:

SampleDocument{name='Bob Lee', date=Sun May 01 00:00:00 IST 2022, age=35}
SampleDocument{name='Amy Williams', date=Fri Jul 01 00:00:00 IST 2022, age=40}

Regards,
Mohamed Elshafey