CODEWITHSUNDEEP
mongodbaggregation-framework
unknowndomain
unknowndomain

Reputation: 985

MongoDB counting days per user

I am trying to create a query that counts how many times a user has done an action in a 1 month period.

This is the data format:

[
  {
    "happened": ISODate(2017-1-1),
    "user": ObjectId(a),
    "action": ObjectId(i)
  },
  {
    "happened": ISODate(2017-1-1),
    "user": ObjectId(a),
    "action": ObjectId(i)
  }
]

I want to find out how many times a user has done a certain action in a period of time.

I've figured out the $match:

{
  $match: {
    happened: { $gte: 2017-1-1, $lt: 2017-2-1 },
    action: i
  }
}

I've also been experimenting with the $group bit, but I can't figure out how to get what I want. This is what I have so far:

{
  $group: {
    _id: {
      user: "$user",
      year: { $year: "$happened" },
      day: { $dayOfYear: "$happened" }
    },
    count: { $sum: 1 }
  }
}

I want to see for each user how many days they perform the action. But I cannot get beyond this:

[
  {
    "_id": {
      "user": ObjectId(a),
      "year": ObjectId(2017),
      "day": ObjectId(1)
    },
    "count": 1
  },
  {
    "_id": {
      "user": ObjectId(a),
      "year": ObjectId(2017),
      "day": ObjectId(2)
    },
    "count": 1
  }
]

You can see that I am getting the count of instances for each user, but it also shows per day. I want to see how many days for each user.

Upvotes: 0

Views: 552

Answers (2)

s7vr
s7vr

Reputation: 75954

You can try below aggregation.

First $group to get distinct days for the each user followed by $group to $push the distinct days and $project to count the days.

{ $group: { _id: { user: "$user", day: { $dayOfMonth: "$happened" } } } },
{ $group: { _id: "_id.$user", days:{$push:"$_id.day"} } },
{ $project: { user:"$user", count: { $size: "$days" } } }

Upvotes: 1

Bertrand Martel
Bertrand Martel

Reputation: 45422

You can $push the root document to a temporary array, $unwind it and make a group again taking the first value for the previous calculated count of days :

db.data.aggregate([{
    $match: {
        happened: { $gte: ISODate("2017-01-01"), $lt: ISODate("2017-02-01") },
        action: "Action1"
    }
}, {
    $group: {
        _id: {
            day: { $dayOfMonth: "$happened" }
        },
        dayCount: { $sum: 1 },
        doc: { $push: "$$ROOT" }
    }
}, {
    $unwind: "$doc"
}, {
    $group: {
        _id: "$doc.user",
        dayCount: { $first: "$dayCount" },
        actionCount: { $sum: 1 }
    }
}])

It gives you userId, dayCount (number of day where at least one action performed) and actionCount : 

{ "_id" : "1", "dayCount" : 2, "actionCount" : 2 }
{ "_id" : "2", "dayCount" : 3, "actionCount" : 4 }

Upvotes: 1

Related Questions