MongoDB : Count of distinct elements of array

Question

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.

I tried using following aggregation query but it took so much time as i have around 5 million records in my collection. db.userDetails.aggregate( { $match: { userid:{$in:[225144,3]} } }, {$unwind: "$open"}, { $match: { "open.viewed":{$gte:15010100, $lte:15063048} } }, { $group: {_id:"$userid", open: {$addToSet: "$open"}} }, {$out: "segment"}) — Abhinandan Kothari
– Abhinandan Kothari, Commented Dec 4, 2015 at 11:51

skmahawar · Accepted Answer · 2015-12-04 13:09:24Z

3

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 } } }
])

edited Dec 4, 2015 at 13:09

answered Dec 4, 2015 at 12:38

skmahawar

3111 silver badge13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Abhinandan Kothari Over a year ago

I have changed my dates to Integers. i.e. '2015-11-30 12:00:00' => 15113012 I used following query now and it is still giving me empty result. db.userDetails.aggregate( [{$unwind: "$open"}, { $match: { "open.viewed":{$gte:15010100, $lte:15123048 } } }, { $group: {_id:"$userid", open_messageids: {$addToSet: "$open.messageid"}} }, { $unwind: "$open_messageids"}, { $group: { _id:"$userid", count: { $sum: 1 } } },{ $match: { count : { $gte : 3 } } }, {$out:"segment"} ])

skmahawar Over a year ago

please check 'viewed' date type

Abhinandan Kothari Over a year ago

Sorry i missed something then, actual result that i got is { "_id" : null, "count" : 118314 } I want records with userid.

skmahawar Over a year ago

last group by will be { _id: null... not _id:"$userId"

Abhinandan Kothari Over a year ago

Hi @skmahwar, it is working fine. Thanks for the help. I just changed "$userid" by "_id" in last $group.

Collectives™ on Stack Overflow

MongoDB : Count of distinct elements of array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related