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
}
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.