CODEWITHSUNDEEP
mongodbselectmongodb-querydistinct
Abhinandan Kothari
Abhinandan Kothari

Reputation: 303

MongoDB : Count of distinct elements of array

I have following documents in MongoDB's collection :

{
    "userid" : 1,
    "open" : [
        {
            "messageid" : 1441,
            "viewed" : ISODate("2015-03-24T00:50:00Z"),
        },
        {
            "messageid" : 1441,
            "viewed" : ISODate("2015-06-21T00:10:00Z"),
        },
        {
            "messageid" : 1527,
            "viewed" : ISODate("2015-06-21T07:52:00Z"),
        }
    ]
}
{
    "userid" : 2,
    "open" : [
        {
            "messageid" : 2155,
            "viewed" : ISODate("2015-09-15T15:32:00Z"),
        },
        {
            "messageid" : 2208,
            "viewed" : ISODate("2015-10-02T12:20:00Z"),
        },
        {
            "messageid" : 2263,
            "viewed" : ISODate("2015-01-15T07:50:00Z"),
        }
    ]
}

I want users who have viewed 3 or more distinct messages in a specified time period.

Example Query:

Give me users those who have viewed 3 or more distinct messages between 2015-01-01 to 2015-11-30

Expected Result: {userid: 2}

Note: (userid: 1) has also viewed 3 messages in specified date range but they are not distinct messages.

Please help me build the MongoDB select query.

Upvotes: 1

Views: 560

Answers (1)

skmahawar
skmahawar

Reputation: 311

You can use aggregation, such as 

db.userDetails.aggregate( [
    { $unwind: "$open" }, 
    { $match: { 
        "open.viewed": { $gte: ISODate( new Date("2015-01-01").toISOString()), $lte: ISODate( new Date("2015-11-30").toISOString()) 
        } 
    }}, 
    { $group: {
        _id: "$userid", 
        open_messageids: { $addToSet: "$open.messageid" }
    }}, 
    { $unwind: "$open_messageids" }, 
    { $group: { 
        _id: null, 
        count: { $sum: 1 } 
    }},
    { $match: { count : { $gte : 3 } } }
])

Upvotes: 3

Related Questions