How to get individual counts of columns in a collection using mongodb query?

Question

I am beginner in MongoDB. I need to get individual column counts from one single MongoDB query.

For example I have a collection hotelCollection with fields cityid , starRating, area, locality. Now I will pass parameter to cityid as 123 to the query and I need to fetch the result as:

result: {
    starCount: {
        1star: 5,
        2star: 6,
        3star: 5
    },
    areaCount: {
        area1: 4,
        area2: 12
    },
    localityCount: {
        locality1: 10,
        locality2: 6
    }
}

I tried using aggregate query but I am getting only one column count in the end. So I ended up writing 4 aggregate queries for the time being. Now I am facing performance issues. I have done lot googling, but couldn't find any solution. Kindly help me out to solve this issue.

Answer 1

You need a conditional expression in your $sum operator that will check for the cityid , starRating, area, locality keys' values by using the comparison operator $eq.

You can restructure your pipeline by adding this expression as follows:

db.hotelCollection.aggregate([
    { "$match": { "cityid": 123 } },
    {
        "$group" : {
            "_id": null, 
            "1star": {
                "$sum": {
                    "$cond": [
                        { "$eq": ["$starRating", 1]  },
                        1, 0
                    ]
                }
            },
            "2star": {
                "$sum": {
                    "$cond": [
                        { "$eq": ["$starRating", 2]  },
                        1, 0
                    ]
                }
            },
            "3star": {
                "$sum": {
                    "$cond": [
                        { "$eq": ["$starRating", 3]  },
                        1, 0
                    ]
                }
            },
            "area1": {
                "$sum": {
                    "$cond": [
                        { "$eq": ["$area", 1]  },
                        1, 0
                    ]
                }
            },
            "area2": {
                "$sum": {
                    "$cond": [
                        { "$eq": ["$area", 2]  },
                        1, 0
                    ]
                }
            },
            "locality1": {
                "$sum": {
                    "$cond": [
                        { "$eq": ["$locality", 1]  },
                        1, 0
                    ]
                }
            },
            "locality2": {
                "$sum": {
                    "$cond": [
                        { "$eq": ["$locality", 2]  },
                        1, 0
                    ]
                }
            }
        }
    },
    { 
        "$project" : { 
            "_id": 0,
            "starCount": {
                "1Star": "$1star",
                "2Star": "$2star",
                "3Star": "$3star"
            },
            "areaCount": {
                "area1": "$area1",
                "area2": "$area2"
            },
            "localityCount": {
                "locality1": "$locality1",
                "locality2": "$locality2"
            }               
        } 
    }
])