CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
TheTurkish
TheTurkish

Reputation: 176

How to perform count(column) in mongodb with aggregation?

I am trying to do to the equivalent of the following query in mongodb:

select count(*), count(category), sum(price) from sales group by usergender

Here is what the documents in my collection look like:

{
    "_id" : ObjectId("54da8b0aa7c80aed4a9f9f33"),
    "userincome" : "$100,000 - $200,000",
    "county" : "Los Angeles",
    "userstate" : "California",
    "usercity" : "Los Angeles",
    "price" : 100,
    "category" : "Swimwear",
    "usergender" : "Male"
}

Here is my aggregation which returns count(*) and sum(price) but I am not sure how to add in count(category).

db['stream.sales'].aggregate([  
   {  
      $group:{  
         _id:"$usergender",
         price:{  
            $sum:"$price"
         },
         _count:{  
            $sum:1
         }
      }
   }
])

I know I can run a separate aggregation to get count(category) but I would like to do it in aggregation, because I don't want all my results filtered where category exists = true.

db['stream.sales'].aggregate([  
   {  
      $match:{  
         'category':{  
            "$exists":true
         }
      }
   },
   {  
      $group:{  
         _id:"$usergender",
         count:{  
            $sum:1
         }
      }
   }
]);

Edit:

Was able to find the solution with the help of wdberkleys response: 

  db['stream.sales'].aggregate([
        { "$group" : { 
            "_id" : "$usergender",
            "count" : { "$sum" : 1 },
            "price" : { "$sum" : "$price" },
            "category" : { "$push" : "$category" }
        } },
        { "$project" : {
            "count" : 1,
            "size" : 1,
            "categories" : { "$size" : "$category" } 
        } }
    ])

Upvotes: 1

Views: 1768

Answers (1)

wdberkeley
wdberkeley

Reputation: 11671

Push the categories to a set during the $group, then $project the size of the resulting set of categories:

db.stream.sales.aggregate([
    { "$group" : { 
        "_id" : "$usergender",
        "count" : { "$sum" : 1 },
        "price" : { "$sum" : "$price" },
        "categories" : { "$addToSet" : "$category" }
    } },
    { "$project" : {
        "count" : 1,
        "size" : 1,
        "categories" : { "$size" : "$category" } 
    } }
])

Upvotes: 2

Related Questions