CODEWITHSUNDEEP
mongodbschemaaggregation-framework
benjiman
benjiman

Reputation: 4048

MongoDB: Filter using data from multiple collections

I have an application that stores users and their behavior in the form of events. There are two collections, one for the users and one for the events. The documents look like this:

User

{
    "_id" : ObjectId("593aa71e2f9d5140000bb44e"),
    "name" : "Antonette Ortiz",
    "country" : "France"
}

Event

{
    "_id" : ObjectId("593aaa84c685604066a6a0cf"),
    "name" : "message_sent",
    "timestamp" : ISODate("2016-11-01T04:39:52.667Z"),
    "user" : ObjectId("593aa728d135484002399bac"),
    "attributes" : {
        "str" : "plum",
        "int" : 82
    }
}

Now I want to be able to fetch a list of users not only by their attributes, but also in regard to the events they have triggered and with respect to a certain timeframe.

An example query would be something like: "All users from France that have at least sent 3 messages within the last 7 days".

How can I achieve this using MongoDB, also in regard to performance (if I have several million events for example)? Is this even possible with just the two collections, or do I have to use aggregation / map reduce? If so, what would you recommend how to change in the schema?

Upvotes: 3

Views: 3582

Answers (1)

Rubin Porwal
Rubin Porwal

Reputation: 3845

According to MongoDB documentation

The $lookup stage does an equality match between a field from the input documents with a field from the documents of the “joined” collection.

For eg

"All users from France that have at least sent 3 messages within the last 7 days".

To retrieve data for above mentioned criteria its aggregate query will be as follows

db.Event.aggregate(

    // Pipeline
    [
        // Stage 1
        {
            $match: {
            name:'message_sent',
            timestamp:{$gte:ISODate("2016-10-25T04:39:52.667+0000"),$lte:ISODate("2016-11-01T04:39:52.667+0000")}
            }
        },

        // Stage 2
        {
            $group: {
               _id:{user:'$user'},
               counter:{$sum:1}
            }
        },

        // Stage 3
        {
            $lookup: {
                "from" : "User",
                "localField" : "_id.user",
                "foreignField" : "_id",
                "as" : "user"
            }
        },

        // Stage 4
        {
            $match: {
             'user.country':'France' ,
            counter:{$gte:3}
            }
        },

    ]



);

Upvotes: 4

Related Questions