sum of daily activities over a month of July MongoDB aggregate

Question

I need to get the sum of steps done per day in the month of July. I am expecting 31 results.

Here is a sample from the database:

/* 1 */
{
    "_id" : ObjectId("5ff0a5878da39cdbf696ffea"),
    "dateTime" : ISODate("2020-07-03T17:20:00.000Z"),
    "value" : "14"
}

/* 2 */
{
    "_id" : ObjectId("5ff0a5878da39cdbf696ffeb"),
    "dateTime" : ISODate("2020-07-03T17:43:00.000Z"),
    "value" : "57"
}

The "value" is the steps done.

Please don't provide images, use formatted text for sample data, for what you have tried and expected result. — Wernfried Domscheit, Jan 06 '21 at 21:11
since July has 31 days mate. the question says, number of steps done PER DAY in the month of JULY. — readymade, Jan 06 '21 at 21:26
How is the number of steps (n.b. what is a "step") per day related to number of days per months? — Wernfried Domscheit, Jan 06 '21 at 21:30
"dateTime" : ISODate("2020-07-03T17:43:00.000Z"), "value" : "57" This means the guy has made 57 steps in the given date and time. so i need to get the sum of the steps he made every day in the month of July. SO technically, i need to find day1:100 steps.......day2:150 steps....etc for the whole month. — readymade, Jan 06 '21 at 21:42
But 57 steps per day gives: day1: 57 steps, day2: 114 steps, day3: 171 steps, ... Or if you like to get the sum, the result is 14+57 = 71. — Wernfried Domscheit, Jan 07 '21 at 07:21

R2D2 · Answer 1 · 2021-01-06T22:50:06.263

0

As per requested maybe something like this will do the job:

db.the_steps_collection.aggregate([ {$project:{ byDay:{$substr:["$dateTime",0,10]},_id:0 , val:{$toInt:"$value"}   }} ,{$match:{ byDay:/^2020-07/   }}  , {$group:{_id:"$byDay" , perDayTotal:{$sum:"$val"}}}   ])

or this seems to be faster:

db.the_steps_collection.aggregate([ {$project:{ byDay:{$substr:["$dateTime",0,10]},_id:0 , val:{$toInt:"$value"} ,mon:{$month: '$dateTime'}  }} ,{$match:{ mon:7   }}  , {$group:{_id:"$byDay" , perDayTotal:{$sum:"$val"}}}   ])

abit more clean(timezone may differ):

db.the_steps_collection.aggregate([ {$project:{ byDay:{$dayOfMonth:{date:"$dateTime",timezone:"+0200"}},_id:0 , val:{$toInt:"$value"} ,mon:{$month:{date:'$dateTime',timezone:"+0200"}}  }} ,{$match:{ mon:7   }}  , {$group:{_id:"$byDay" , perDayTotal:{$sum:"$val"}}}  ,{$sort:{_id:1}} ])

edited Jan 06 '21 at 22:50

answered Jan 06 '21 at 21:49

R2D2

9,410
2
12
28

You are welcome! ( if you accept the solution please, add the green checkbox ;) ) – R2D2 Jan 06 '21 at 22:09
is there a way of sorting the result? :) – readymade Jan 06 '21 at 22:14
Added $sort stage by dayOfMonth to the latest ... :) – R2D2 Jan 06 '21 at 22:16
You are a star @kiki0075....thanks. But I can't vote as I have less than 15 reputation – readymade Jan 06 '21 at 22:56
Ah no worries , didn't know you cannot accept answer when reputation is low number ... – R2D2 Jan 06 '21 at 23:00
Just found you can accept favorite answer if you wish to , there is no privilege necessary to do it , the privilege is only for voting : https://stackoverflow.com/help/someone-answers – R2D2 Jan 06 '21 at 23:29
How do you cover empty days? TO asked for 31 (always?) results, your query returns one document per day - which might be different. – Wernfried Domscheit Jan 07 '21 at 07:25
another answer added to cover the use case with all 0 count days in month – R2D2 Jan 07 '21 at 13:53

R2D2 · Accepted Answer · 2021-01-07T14:35:41.703

And here is solution if you want to have the days with 0 count with abit of JS help ( you need to provide which month in jsMonth , and which year in jsDayCount ) :

mongos> db.d.find()
{ "_id" : ObjectId("5ff62e4484088fbb4acaf035"), "dateTime" : ISODate("2020-07-03T17:20:00Z"), "value" : "14" }
{ "_id" : ObjectId("5ff62e4484088fbb4acaf036"), "dateTime" : ISODate("2020-07-05T17:20:00Z"), "value" : "14" }
{ "_id" : ObjectId("5ff62e4484088fbb4acaf037"), "dateTime" : ISODate("2020-07-03T17:20:00Z"), "value" : "50" }
{ "_id" : ObjectId("5ff62e4484088fbb4acaf038"), "dateTime" : ISODate("2020-08-10T17:20:00Z"), "value" : "100" }
mongos> var jsMonth=7;var jsDayCount=new Date(2020, jsMonth , 0).getDate();
mongos> db.d.aggregate([ 
{$project:{ byDay:{$dayOfMonth:{date:"$dateTime",timezone:"+0200"}},_id:0 , val:{$toInt:"$value"} ,mon: {$month:{date:'$dateTime',timezone:"+0200"}}  }} ,{$match:{ mon:jsMonth   }}  ,
{$group:{_id:"$byDay" , perDayTotal:{$sum:"$val"}}}    , {$sort:{_id:1}}  , 
{$group:{_id:"total" , arr:{$push:{day:"$_id",perDay:"$perDayTotal"   }} }}  , 
{ $addFields:{ arr:{ $reduce:{ input:{$setDifference:[ {$range:[1,jsDayCount,1]} ,"$arr.day"  ]   }, initialValue:"$arr",in:{ $concatArrays: 
["$$value" ,[{day:"$$this", perDay:0  }] ]     }     }  }  } }   ,
{$unwind:"$arr"}, 
{$project:{_id:0 ,day:"$arr.day" ,    countPerDay:"$arr.perDay"}}   , $sort:{"day":1}} ])
{ "day" : 1, "countPerDay" : 0 }
{ "day" : 2, "countPerDay" : 0 }
{ "day" : 3, "countPerDay" : 64 }
{ "day" : 4, "countPerDay" : 0 }
{ "day" : 5, "countPerDay" : 14 }
{ "day" : 6, "countPerDay" : 0 }
{ "day" : 7, "countPerDay" : 0 }
{ "day" : 8, "countPerDay" : 0 }
{ "day" : 9, "countPerDay" : 0 }
{ "day" : 10, "countPerDay" : 0 }
{ "day" : 11, "countPerDay" : 0 }
{ "day" : 12, "countPerDay" : 0 }
{ "day" : 13, "countPerDay" : 0 }
{ "day" : 14, "countPerDay" : 0 }
{ "day" : 15, "countPerDay" : 0 }
{ "day" : 16, "countPerDay" : 0 }
{ "day" : 17, "countPerDay" : 0 }
{ "day" : 18, "countPerDay" : 0 }
{ "day" : 19, "countPerDay" : 0 }
{ "day" : 20, "countPerDay" : 0 }
{ "day" : 21, "countPerDay" : 0 }
{ "day" : 22, "countPerDay" : 0 }
{ "day" : 23, "countPerDay" : 0 }
{ "day" : 24, "countPerDay" : 0 }
{ "day" : 25, "countPerDay" : 0 }
{ "day" : 26, "countPerDay" : 0 }
{ "day" : 27, "countPerDay" : 0 }
{ "day" : 28, "countPerDay" : 0 }
{ "day" : 29, "countPerDay" : 0 }
{ "day" : 30, "countPerDay" : 0 }
mongos>

sum of daily activities over a month of July MongoDB aggregate

2 Answers2

Linked