CODEWITHSUNDEEP
mongodbmongodb-query
dalf
dalf

Reputation: 602

MongoDB show first and last event for each user for each day

Each time a user logs in or logs out, an event is saved in mongo. A user can login and/or logout multiple times a day.

For example, Bob has login 2 times and logout 1 time:

{
  username: ‘bob’,
  type: ‘login’,
  eventDate: ISODate(‘2018-09-21T12:39:50.676Z’)
}

{
  username: ‘bob’,
  type: ‘login’,
  eventDate: ISODate(‘2018-09-21T13:55:50.676Z’)
}

{
  username: ‘bob’,
  type: ‘logout,
  eventDate: ISODate(‘2018-09-21T22:10:50.676Z’)
}

And James has just 1 login event:

{
  username: ‘james’,
  type: ‘login,
  eventDate: ISODate(‘2018-09-21T10:10:50.676Z’)
}

I would like to execute a query that would retrieve the first login of the day and the last logout of the day for each user for each day (let’s say during the past week).

So the result would be like:

[{
  username: ‘bob’,
  firstLogin: ISODate(‘2018-09-21T12:39:50.676Z’),
  lastLogout: ISODate(‘2018-09-21T22:10:50.676Z’)
}

{
  username: ‘james’,
  firstLogin: ISODate(‘2018-09-22T10:19:50.676Z’),
  lastLogout: null,
}]

I believe I have to deal with ‘aggregation’ but not sure.

Upvotes: 0

Views: 1069

Answers (2)

IftekharDani
IftekharDani

Reputation: 3729

You can achieve by single group.

Also work when you have different days for the same user.

return last and the first date by type.

tested on MongoDB GUI.

db.getCollection("loginDetail").aggregate([
  {
    $group: {
      _id: {
        username: "$username",
        year: { $year: "$eventDate" },
        month: { $month: "$eventDate" },
        day: { $dayOfMonth: "$eventDate" }
      },
      firstLogin: {
        $min: {
          $cond: [{ $and: [{ $eq: ["$type", "login"] }] }, "$eventDate", null]
        }
      },
      lastLogout: {
        $max: {
          $cond: [{ $and: [{ $eq: ["$type", "logout"] }] }, "$eventDate", null]
        }
      }
    }
  },
  {
      $project: {
          _id: 1,
          username : '$_id.username',
          firstLogin :1,
          lastLogout :1
          }
      }
]);

Upvotes: 1

Hardik Shah
Hardik Shah

Reputation: 4210

By using two levels of $group: (MongoDB V3.2.18)

I believe userName is unique.

  1. $sort eventDate first.
  2. $group by userName and type.
  3. $project to differentiate firstLogin and lastLogin.
  4. $group again by userName for final result.
db.getCollection('test').aggregate([
  {$sort: {'eventDate' : 1}},
  {
    "$group" : {
        _id: {"username" : "$username", "type": "$type"},
        eventDate: {$push: "$eventDate"}
    }
  },
  {
    $project : {
      _id:1,
      eventDate:1,
      firstLogin: {
        $cond: [ { "$eq": ["$_id.type", "login" ]}, { $arrayElemAt: [ "$eventDate", 0 ] }, null]
      },
      lastLogout: {
        $cond: [ { "$eq": ["$_id.type", "logout" ]}, { $arrayElemAt: [ "$eventDate", -1 ] }, null]
      }
    }
  },
  {
    "$group" : {
        _id: "$_id.username",
        firstLogin: {$first: "$firstLogin"},
        lastLogout: {$last: "$lastLogout"}
    }
  }
]);

Output: 

/* 1 */
{
    "_id" : "james",
    "firstLogin" : ISODate("2018-09-21T10:10:50.676Z"),
    "lastLogout" : null
}

/* 2 */
{
    "_id" : "bob",
    "firstLogin" : ISODate("2018-09-21T12:39:50.676Z"),
    "lastLogout" : ISODate("2018-09-21T22:10:50.676Z")
}

Upvotes: 2

Related Questions