count and count unique in a single mongo aggregate query

Question

I have a mongo aggregate query which calculates COUNT, MIN, MAX and BLANK counts of different keys.

db.getCollection('ReportTestProcess').aggregate([
   $group": {
                "_id":0,
                "Inititated_on_MIN": {
                    "$min": "$Inititated_on.v"
                },
                "Inititated_on_MAX": {
                    "$max": "$Inititated_on.v"
                },
                "Text_COUNT":{$sum:1},
                "Text_BLANK": {
                    "$sum": {
                        "$cond": [
                            {
                                "$ifNull": [
                                    "$Inititated_on.v",
                                    false
                                ]
                            },
                            0,
                            1
                        ]
                    }
                }
}])

Now I want UNIQUE_COUNT of elements along with it. The only way I could think of doing it is to group based the fields but grouping will affect the results of MIN, MAX or COUNT

You can use $addToSet (docs.mongodb.com/manual/reference/operator/aggregation/addToSet) to get unique values. — Mayuri S Kulkarni
– Mayuri S Kulkarni, Commented Dec 26, 2018 at 7:30
where is the _id expression in the $group stage here? and what is the Text here? — Ashh
– Ashh, Commented Dec 26, 2018 at 7:46
You can use $addToSet here, { "UNIQUE_TEXT": { "$addToSet": "YOUR_FIELD" }} and then use one $project stage to find the length of the UNIQUE_TEXT array or either use $facet aggregation — Ashh
– Ashh, Commented Dec 26, 2018 at 7:54

Ravi Kumar Gupta · Accepted Answer · 2018-12-26 07:29:53Z

1

You can use $addToSet

Sets always keep unique values. Your code should look like.

db.getCollection('ReportTestProcess').aggregate([
   $group": {
            "Inititated_on_MIN": {
                "$min": "$Inititated_on.v"
            },
            "Inititated_on_MAX": {
                "$max": "$Inititated_on.v"
            },
            "Text_COUNT":{$sum:1},
            "Text_UNIQUE": { $addToSet: "$Inititated_on.v" }
            "Text_BLANK": {
                "$sum": {
                    "$cond": [
                        {
                            "$ifNull": [
                                "$Inititated_on.v",
                                false
                            ]
                        },
                        0,
                        1
                    ]
                }
            }
}])

answered Dec 26, 2018 at 7:29

Ravi Kumar Gupta

1,7882 gold badges24 silver badges41 bronze badges

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

1 Comment

Bhavani Ravi Over a year ago

Works Like a charm paired it up with a project stage and use $size to get the count

Collectives™ on Stack Overflow

count and count unique in a single mongo aggregate query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related