Reputation: 176
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
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