Mongo does not return documents in aggregation using $gte and $date

Question

In the MongoDB (v3.2.8) I have some bucketed values stored in a time sequence. I'm currently trying to aggregate data out of these buckets using the Morphia framework (v1.1.0), limiting the documents by the date of the document (a field, see sample below).

{
"_id" : ObjectId("57b696548376400e6e56a18a"),
"date" : ISODate("2016-08-19T00:00:00.000Z"),
"kpiId" : "1.2",
"history" : [ 
    {
        "name" : "02. Chilled Water Temperature",
        "timestamp" : ISODate("2016-08-19T05:28:29.343Z"),
        "value" : "6"
    }, 
    {
        "name" : "02. Chilled Water Temperature",
        "timestamp" : ISODate("2016-08-19T05:28:54.721Z"),
        "value" : "1"
    }, 
    {
        "name" : "02. Chilled Water Temperature",
        "timestamp" : ISODate("2016-08-19T05:30:31.003Z"),
        "value" : "21"
    }, 
    {
        "name" : "02. Chilled Water Temperature",
        "timestamp" : ISODate("2016-08-19T05:31:58.458Z"),
        "value" : "20"
    }
],
"asset" : {
    "id" : "1",
    "name" : "LTD121",
    "contract" : {
        "id" : "MyCompany",
        "name" : "MyCompany"
    }
},
"count" : 4
}

For background information I'm using the Morphia framework to generate the queries to MongoDB. When I aggregate with Morphia using the date and kpiId and asset.contract.id generates the following match query:

{ "$match" : {
   "asset.contract.id" : "MyCompany" , 
   "kpiId" : "1.2",
   "date" : { "$gte" : { "$date" : "2016-08-19T00:00:00.000Z"}}
}}

This query however does not return any documents in the collection, when I expected the document in the sample to be returned. It gets even stranger when I manually change the query to the query below I do get the document from MongoDB.

{ "$match" : {
   "asset.contract.id" : "MyCompany",
   "kpiId" : "1.2" , 
   "date" : { "$gte" : ISODate("2016-08-19T00:00:00.000Z")}
}}

Why is the first query not working, and if it is an invalid or incorrect query how do I manipulate Morphia to create the query correctly.

Update: per request adding the related Java code that we use to instruct morphia to create the query:

datastore.createAggregation(HistoryBucket.class)
    .match(datastore.createQuery(HistoryBucket.class)
        .field("asset.contract.id").equal(contractId)
        .field("kpiId").equal(kpiId)
        .field("date")
           .greaterThanOrEq(CalendarUtils.truncateToDayUTC(startDate)))
     .aggregate(HistoryBucket.class);

In this the startDate is of type java.util.Date and the return of the call to CalendarUtils.truncateToDayUTC also returns a java.util.Date with the minutes, hours and seconds set to 0.

Hi gerben84, and welcome to Stack Overflow. Your question looks good, with lots of relevant and well-presented information, but there's just one thing missing: can you show your morphia code which generates that aggregation? — Vince Bowdren, Aug 19 '16 at 07:07
Hi Vince Bowdren, I've added the relevant Java code we use to instruct Morphia to query for the data. — , Aug 19 '16 at 07:56

score 0 · Answer 1 · edited May 23 '17 at 11:51

The reason for your query not working in Mongo Shell is as follows

$date in the schema is stored as "date" : ISODate("2016-08-19T00:00:00.000Z"),

whereas in the aggregate query you are using it as a String "date" : { "$gte" : { "$date" : "2016-08-19T00:00:00.000Z"}}, So the query is not fetching any records and when you change the aggregate query to "date" : { "$gte" : ISODate("2016-08-19T00:00:00.000Z")} you are getting the result.

More Explanation

We are using $match in aggregate pipeline and the elements we are comparing are "asset.contract.id", "kpiId" and date in our schema.

Now if we again specify "date" : { "$gte" : { "$date" : ISODate("2016-08-19T00:00:00.000Z")}}, we are lost in finding an element which is date.date. We don't need this $date specified inside $gte.

The final query would be

db.yourCollectionName.aggregate([{ "$match" : {
 "asset.contract.id" : "MyCompany",
 "kpiId" : "1.2",
 "date" : { "$gte" : ISODate("2016-08-19T00:00:00.000Z")}
}}])

If you want you can use new Date instead of ISODate both will yield the same result and also you need to pass the date value thru a variable.

References

https://docs.mongodb.com/manual/reference/operator/query/gte/

Find objects between two dates MongoDB

Mongo does not return documents in aggregation using $gte and $date

1 Answers1