CODEWITHSUNDEEP
mongodbaggregation-framework
Daksh
Daksh

Reputation: 986

Find active users using MongoDB's aggregation framework

I have two collections — customers and trips:

An example document in customers where _id is a unique ID assigned to each customer:

{
    "_id": ObjectId("59caa2660d09740010eccfde")
}

An example document in trips where userId is the _id of the customer who took the trip, duration is the trip duration in minutes and endtime is the time at which that particular trip ended (_id here doesn't really matter):

{
    "_id" : ObjectId("59fe73be50fe7600169bcdb5"),
    "userId": ObjectId("59caa2660d09740010eccfde"),
    "duration": NumberInt(54),
    "endtime": ISODate("2017-11-03T08:14:45.193+0000")
}

On using $lookup to "left outer join" trips to customers, I have several hundred documents and each customer has trips (zero or more) associated with them:

{
    "_id": ObjectId("59caa2660d09740010eccfde"),
    "trips": [
      {
          "_id" : ObjectId("59fe73be50fe7600169bcdb5"),
          "userId": ObjectId("59caa2660d09740010eccfde"),
          "duration": NumberInt(54),
          "endtime": ISODate("2017-11-03T08:14:45.193+0000")
      },
      {
          "_id" : ObjectId("5a5acb708e303e00109820ac"),
          "userId": ObjectId("59caa2660d09740010eccfde"),
          "duration": NumberInt(32),
          "endtime": ISODate("2018-01-14T03:15:59.207+0000")
      }
    ]
}

I want to fetch all active customers where an active customer is anyone who:

Upvotes: 1

Views: 672

Answers (1)

chridam
chridam

Reputation: 103395

You would want to have the $lookup pipeline stage as the last or one of the last after grouping the trips collection calculating the aggregates and then filter.

So, for the first condition you need to get all the last four weeks documents from the trips collection, group them by userId and one week interval and aggregate the times:

var fourWeeksAgo = new Date();
fourWeeksAgo.setDate(fourWeeksAgo.getDate() - 28);

// var fourWeeksAgo = moment().subtract(4, "weeks").toDate();

db.trips.aggregate([
    { "$match": { "endtime": { "$gte": fourWeeksAgo } } },
    {
        "$group": {
            "_id": {
                "user": "$userId",
                "weekInterval": {
                    "$subtract": [
                        { 
                            "$subtract": ["$endtime", new Date("1970-01-01")] 
                        },
                        { 
                            "$mod": [
                                { "$subtract": ["$endtime", new Date("1970-01-01")] },
                                1000 * 60 * 60 * 24 * 7
                            ]
                        }
                    ]
                }
            },
            "count": { "$sum": 1 },
            "totalDuration": { "$sum": "$duration" }
        }
    },
    {
        "$group": {
            "_id": "$_id.user",
            "weekCounts": {
                "$push": {
                    "week": "$weekInterval",
                    "count": "$count",
                    "duration": "$totalDuration"
                }
            }
        }
    },
    {
        "$match": {
            "weekCounts.1": { "$exists": true },
            "$or": [
                { "weekCounts.count": { "$gte": 7 } },
                { "weekCounts.duration": { "$gte": 300 } }
            ]
        }
    },
    {
        "$lookup": {
            "from": "users",
            "localField": "_id",
            "foreignField": "_id",
            "as": "user"
        }           
    }
])

Upvotes: 1

Related Questions