CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
MChan
MChan

Reputation: 7182

Mongodb aggregate not grouping documents by date

I have the following 3 documents in my MongoDB:

[Object_id: "tMSfNq9JR85XDaQe5"date: Sun Dec 07 2014 19:50:21 GMT+0800 (HKT)description: "Test" projectid: "S83NEGHnrefvfASrf"totalseconds: 22200__proto__: Object, 

Object_id: "FeyzdMosaXCht8DKK"date: Mon Dec 15 2014 00:00:00 GMT+0800 (HKT)description: "444"projectid: "S83NEGHnrefvfASrf"totalseconds: 3660__proto__: Object, 

Object_id: "cCKByxSdQMHAsRKwd"date: Mon Dec 15 2014 00:00:00 GMT+0800 (HKT)description: "555"projectid: "S83NEGHnrefvfASrf"totalseconds: 3660__proto__: Object]

I am trying to run the following aggregate pipeline on it to group the sum of totalseconds by date so that the end result will be something like shown below, yet each time I get the result as 3 records exactly just as input 3 documents...can someone please tell me what I might be doing wrong / missing here? Thanks

var pipeline = [
  {$group:
        {_id:{"projectId":"$projectid", "date":"$date"},
         totalHrs:{$sum:"$totalseconds"}
        }
  }
];


 { "Date":"Sun Dec 07 2014 19:50:21 GMT+0800 (HKT)",
   "totalseconds": "22200"
 },
 { "Date":"Sun Dec 15 2014 00:00:00 GMT+0800 (HKT)",
   "totalseconds": "7320"
 }

Upvotes: 0

Views: 1273

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151132

All you are supplying here is the existing "date" value as part of the grouping key. It is exactly what it says it is and is likely to be very granular, i.e to the millisecond.

What you want here is to just use the "day" part of the recorded timestamp value in your date type field. For this you can use the date operators of the aggregation framework:

db.collection.aggregate([
    { "$group": {
        "_id": { 
            "projectid": "$projectid",
            "day": { "$dayOfMonth": "$date" },
            "month": { "$month": "$date" },
            "year": { "$year": "$date" }
        },
        "totalseconds": { "$sum": "$totalseconds" }
    }}
])

Or just use date math on your date objects and round the values to a day:

db.collection.aggregate([
    { "$group": {
        "_id": { 
            "projectid": "$projectid",
            "date": {
                "$subtract": [
                    { "$subtract": [ "$date", new Date("1970-01-01") ]},
                    { "$mod": [
                        { "$subtract": [ "$date", new Date("1970-01-01") ]},
                        1000 * 60 * 60 * 24
                    ]}
                ]
            }
        },
        "totalseconds": { "$sum": "$totalseconds" }
    }}
])

Either way, you want just part of the date field and not all of it. Otherwise there is nothing to actually group on unless exact time values are the same.

Upvotes: 1

Related Questions