Search code examples
phpmongodbphp-mongodbdatabase

How do I using Mongo aggregation to get the total "page views" for each group of days


I have tried for a few days now to come up with a working aggregate but I'm just not getting it.

I have a collection that looks similar to the following.

_id: ObjectId("52672ca368a56f481d000045"),  
totalviews: 5, 
views: [ 
  {date: ISODate("2013-10-23T01:55:47Z") },
  {date: ISODate("2013-10-23T01:55:50Z") },
  {date: ISODate("2013-10-23T23:31:08Z")},
  {etc.}
]

What I'm trying to do is get a total number of views for each day so I can display a list of dates (from "views") and the total for those days.

Can someone show me how this can be done?

Thank you

Edit - Below Code Works Correctly Here is the php code for the solution provided in case it helps someone else:

$ops = array(
array('$unwind' => '$views'),
array('$project' => array('date'=>array(
    'day'=>array('$dayOfMonth'=>'$views.date'),
    'month'=>array('$month'=>'$views.date'),
    'year'=>array('$year'=>'$views.date'))
)),
array('$group'=>array('_id'=>'$date','views'=>array('$sum'=>1))) 
);

$data = $collection->aggregate($ops);

Solution

  • This should do the trick.

    db.foo.aggregate(
        {$unwind: "$views"},    
        {$project: {date: {
            day: {$dayOfMonth: "$views.date"},
            month: {$month: "$views.date"},
            year: {$year: "$views.date"},
        }}},
        {$group: {_id: {id: "$_id", date: "$date"}, views: {$sum: 1}}}
     )
    

    If you want total number of views per day for collection replace group phase with this one:

    {$group: {_id: "$date", views: {$sum: 1}}}