CODEWITHSUNDEEP
mongodbnosqlaggregation-frameworkaggregate
AVEbrahimi
AVEbrahimi

Reputation: 19224

MongoDB count distinct items

I have following query on a list with this fields : key,time,p,email

use app_db;
db.getCollection("app_log").aggregate(
    [
        { 
            "$match" : {
                "key" : "login"
            }
        }, 
        { 
            "$group" : {
                "_id" : {
                    "$substr" : [
                        "$time", 
                        0.0, 
                        10.0
                    ]
                }, 
                "total" : {
                    "$sum" : "$p"
                }, 
                "count" : {
                    "$sum" : 1.0
                }
            }
        }
    ]
);

and the output is something like this :

{ 
    "_id" : "2019-08-25", 
    "total" : NumberInt(623), 
    "count" : 400.0
}
{ 
    "_id" : "2019-08-24", 
    "total" : NumberInt(2195), 
    "count" : 1963.0
}
{ 
    "_id" : "2019-08-23", 
    "total" : NumberInt(1294), 
    "count" : 1706.0
}
{ 
    "_id" : "2019-08-22", 
    "total" : NumberInt(53), 
    "count" : 1302.0
}

But I need the count to be distinctive on email field, which is count number of distinct email addresses who logged in per day and their p value is greater 0

Upvotes: 1

Views: 51

Answers (1)

mickl
mickl

Reputation: 49995

You need $addToSet to get an array of unique email values per day and then you can use $size to count the number of items in that array:

db.getCollection("app_log").aggregate(
    [
        { 
            "$match" : {
                "key" : "login"
            }
        }, 
        { 
            "$group" : {
                "_id" : {
                    "$substr" : [
                        "$time", 
                        0.0, 
                        10.0
                    ]
                }, 
                "total" : {
                    "$sum" : "$p"
                }, 
                "emails" : {
                    "$addToSet": "$email"
                }
            }
        },
        {
            $project: {
                _id: 1,
                total: 1,
                countDistinct: { $size: "$emails" }
            }
        }
    ]
);

Upvotes: 1

Related Questions