MongoDb aggregation count distict field value

Question

I have following db values

{ 
    "_id" : ObjectId("5875d82f8447f454b37a947b"), 
    "updatedAt" : ISODate("2017-01-11T07:01:03.570+0000"), 
    "createdAt" : ISODate("2017-01-11T07:01:03.570+0000"), 
    "__v" : NumberInt(0), 
    "activity_type" : "email_campaign_delivery", 
    "customer_id" : ObjectId("581ad6f4d1fb4a0e14af159c"), 
    "email_campaign_id" : ObjectId("5875d82f8447f454b37a9476"), 
    "email" : "[email protected]", 
    "delivered" : true
}
{ 
    "_id" : ObjectId("5875d82f8447f454b37a947c"), 
    "updatedAt" : ISODate("2017-01-11T07:01:03.570+0000"), 
    "createdAt" : ISODate("2017-01-11T07:01:03.570+0000"), 
    "__v" : NumberInt(0), 
    "activity_type" : "email_campaign_delivery", 
    "customer_id" : ObjectId("581ad6f4d1fb4a0e14af159c"), 
    "email_campaign_id" : ObjectId("5875d82f8447f454b37a9476"), 
    "email" : "[email protected]", 
    "delivered" : false
}
{ 
    "_id" : ObjectId("5875d82f8447f454b37a947d"), 
    "updatedAt" : ISODate("2017-01-11T07:01:03.570+0000"), 
    "createdAt" : ISODate("2017-01-11T07:01:03.570+0000"), 
    "__v" : NumberInt(0), 
    "activity_type" : "email_campaign_click", 
    "customer_id" : ObjectId("581ad6f4d1fb4a0e14af159c"), 
    "email_campaign_id" : ObjectId("5875d82f8447f454b37a9475"), 
    "email" : "[email protected]", 
    "clicked" : true
}

I need mongo aggregation query that get total deliver & not deliver result along with total email campaign count my mongo aggregate query is

CampaignActivity.aggregate([{
                        "$group": {
                                "_id": null,
                                "delivered": {
                                    $sum: {
                                        "$cond": {
                                            if: {
                                                $eq: ["$delivered", true]
                                            },
                                            then: 1,
                                            else: 0
                                        }
                                    }
                                },
                                "not_delivered": {
                                    $sum: {
                                        "$cond": {
                                            if: {
                                                $eq: ["$delivered", false]
                                            },
                                            then: 1,
                                            else: 0
                                        }
                                    }
                                },
                                "total_recipient": {
                                    $sum: {
                                        "$cond": {
                                            if: {
                                                $eq: ["$activity_type", "email_campaign_delivery"]
                                            },
                                            then: 1,
                                            else: 0
                                        }
                                    }
                                }


                            }

                    }, {
                        "$project": {
                            "_id": 0,
                            "not_delivered":1,
                            "total_recipient":1,
                            "delivered": 1
                        }
                    }],function (err, result) {
                                if (err) {
                                    console.log(err)
                                } else {
                                    console.log(result)
                                }
                    });

Got following result

{
  "delivered": 2,
  "not_delivered": 7,
  "total_recipient": 9
}

How can i get "email_campaign_id" count without duplicate? by using above example its must be 2.

Expected output :

{ "delivered": 2, "not_delivered": 7, "total_recipient": 9, "total_campaign":2 }

try CampaignActivity.distinct("email_campaign_id"). This returns an array with all distinct "email_campaign_id". The count is the size of the array — felix
– felix, Commented Jan 12, 2017 at 6:08
@LakmalVithanage expected output : { "delivered": 2, "not_delivered": 7, "total_recipient": 9, "total_campaign":2 } — Kepler
– Kepler, Commented Jan 12, 2017 at 6:20

HoefMeistert · Accepted Answer · 2017-01-12 06:36:23Z

What you can do is in the group stage add the email address to an array, I use $addToSet so no duplicates are added. In the next stage project the values and use $size to show the number of unique email addresses. Below my example.

db.collection.aggregate(
    [
        {
            $group: {
                                            "_id": null,
                                            "delivered": {
                                                $sum: {
                                                    "$cond": {
                                                        if: {
                                                            $eq: ["$delivered", true]
                                                        },
                                                        then: 1,
                                                        else: 0
                                                    }
                                                }
                                            },
                                            "not_delivered": {
                                                $sum: {
                                                    "$cond": {
                                                        if: {
                                                            $eq: ["$delivered", false]
                                                        },
                                                        then: 1,
                                                        else: 0
                                                    }
                                                }
                                            },
                                            emails : { $addToSet: "$email"}
                                        }
        },
        {
            $project: {
            delivered :1,
            not_delivered : 1,
            total_reciepient : { $size: "$emails" }
            }
        },

    ]    
);

arunraj770 · Accepted Answer · 2017-01-12 06:36:15Z

0

Group that distinct items u need into an array and then use $size to get that array count. $size can be used with $aggregate

https://docs.mongodb.com/manual/reference/operator/aggregation/size/

answered Jan 12, 2017 at 6:36

arunraj770

8872 gold badges12 silver badges30 bronze badges

Collectives™ on Stack Overflow

MongoDb aggregation count distict field value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related