MongoDB aggregate query

Question

In MongoDB I have a collection:

Statistics
{
    UserID:  int          //User id
    Url: string           //Url
    Clicks: [DateTime]    //A time array    
}

When a user clicks an url add a date of the click date in Clicks array. My question is how can I write an aggregate query such as get a number of clicks that was from [date1] till [date2] and group by UserID? How can I output the, to a file?

Thanks!

Do you mean "group" as in to group the rows or "group" as in sort by UserID so all urls by one user id are in the same area in the result set? — Sammaye
– Sammaye, Commented Oct 26, 2012 at 13:49
I mean group is not the same as sort. It is like SQL command GROUP BY. — Александр Сысоев
– Александр Сысоев, Commented Oct 26, 2012 at 14:11
I think some of your schema is missing here cos: "get a number of clicks that was from [date1] till [date2]" but you have no number, your Clicks field is a Datetime field not a integer field displaying the clicks and you only have the URL field after that. What's the name of your date field? — Sammaye
– Sammaye, Commented Oct 26, 2012 at 14:14
Sammaye, the name of the field is Clicks. The number of the clicks - it is the length of items in Clicks array. — Александр Сысоев
– Александр Сысоев, Commented Oct 26, 2012 at 14:16
That's a very strange schema. normally one would make a doc per click in this case. Hmm this does make it a little more difficult cos you are tying to range over a datetime in many elements in a sub document in many documents...I'll play around a bit. — Sammaye
– Sammaye, Commented Oct 26, 2012 at 14:19

Gianfranco P · Accepted Answer · 2012-10-26 16:18:31Z

Assuming you have data like this (see at the bottom how to generate this):

{ "_id": ObjectId("508ab0e27bb16229520c9561"), "userid": 0, "url": "", "clickDate": ISODate("20120101T12:01:00Z") }
{ "_id": ObjectId("508ab0e27bb16229520c9562"), "userid": 1, "url": "", "clickDate": ISODate("20120202T12:01:00Z") }
{ "_id": ObjectId("508ab0e27bb16229520c9563"), "userid": 2, "url": "", "clickDate": ISODate("20120303T12:01:00Z") }
{ "_id": ObjectId("508ab0e27bb16229520c9564"), "userid": 3, "url": "", "clickDate": ISODate("20120404T11:01:00Z") }
{ "_id": ObjectId("508ab0e27bb16229520c9565"), "userid": 4, "url": "", "clickDate": ISODate("20120505T11:01:00Z") }

Here is the aggregation function:

db.test.aggregate( {
                      $match: {
                        clickDate: { $gte: new Date(2012,8,30,12,0,0) }
                      }
                    },
                    {
                      $group: {
                        _id: "$userid",
                        clicks: { $sum: 1 }
                      }
                    }
                 );

Make sure you have the $match before the $group. See early filtering.

Results:

{
  "result": [
    { "_id": 8,
      "clicks": 1
    },
    { "_id": 7,
      "clicks": 2
    },
    { "_id": 6,
      "clicks": 2
    },
    { "_id": 3,
      "clicks": 2
    },
    { "_id": 2,
      "clicks": 2
    },
    { "_id": 1,
      "clicks": 2
    },
    { "_id": 4,
      "clicks": 2
    },
    { "_id": 0,
      "clicks": 2
    },
    { "_id": 5,
      "clicks": 2
    },
    { "_id": 9,
      "clicks": 1
    }
  ],
  "ok": 1
}

The Data was generated with this loop:

// d=days, m=months (for ISODate months start from 0, while days from 1) 
for (var i = 0, d = 1, m = 0, id = 0; i < 100; i++, d++, m++, id++) {
  if (d > 30){
    d=1;
  }
  if (m > 10){
    m=0;
  }
  if (id > 9){
    id=0;
  }
  db.test.insert({userid: id, url:"", clickDate: new Date(2012,m,d,12,1,0)});
}

unfortunately, as described in the comments, his dates is actually an array, so he wants to match dates in a array in his document and then $unwind those (most likely) and then count the amount matching and then $group by userID. Basically to $sum only the matching dates of an array of dates.

Collectives™ on Stack Overflow

MongoDB aggregate query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related