Ritesh Sinha
Reputation: 840
Aggregate grouping with two keys
I have a following collection in mongodb :
{
"_id" : ObjectId("558c108b209c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 183,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "10",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c108b209c022c947b0056"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 185,
"weekofyear" : 23,
"productcount" : 2,
"count" : 23,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "10",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c108b209c022c947b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 182,
"weekofyear" : 24,
"productcount" : 1,
"count" : 24,
"position" : "2",
"productsclickedid" : "8156",
"sessionid" : "11",
"filtername" : "Sub Category"
}
{
"_id" : ObjectId("558c108b209c022c947b0022"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 186,
"weekofyear" : 26,
"productcount" : 25,
"count" : 25,
"position" : "2",
"productsclickedid" : "8156",
"sessionid" : "11",
"filtername" : "Sub Category"
}
{
"_id" : ObjectId("558c108b209c022c947b0032"),
"term" : "aero",
"year" : 2015,
"month" : 7,
"day" : 1,
"hour" : 17,
"dayofyear" : 182,
"weekofyear" : 26,
"productcount" : 23,
"count" : 12,
"position" : "2",
"productsclickedid" : "8156",
"sessionid" : "11",
"filtername" : "Sub Category"
}
{
"_id" : ObjectId("5348c108b09c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 170,
"weekofyear" : 26,
"productcount" : 235,
"count" : 1,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "10",
"filtername" : "Sub Category"
}
{
"_id" : ObjectId("658c108b209c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 185,
"weekofyear" : 26,
"productcount" : 235,
"count" : 1,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "12",
"filtername" : "price Range"
}
{
"_id" : ObjectId("558c108b209c022c947a0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 181,
"weekofyear" : 26,
"productcount" : 235,
"count" : 1,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "12",
"filtername" : "price Range"
}
{
"_id" : ObjectId("213c108b209c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 184,
"weekofyear" : 27,
"productcount" : 0,
"count" : 27,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "12",
"filtername" : "price Range"
}
{
"_id" : ObjectId("558c108b209c082c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 183,
"weekofyear" : 26,
"productcount" : 0,
"count" : 13,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "12",
"filtername" : "price Range"
}
{
"_id" : ObjectId("5589108b209c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 184,
"weekofyear" : 21,
"productcount" : 0,
"count" : 21,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "12",
"filtername" : "price Range"
}
{
"_id" : ObjectId("558a108b209c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 185,
"weekofyear" : 26,
"productcount" : 0,
"count" : 16,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "13",
"filtername" : "price Range"
}
{
"_id" : ObjectId("558c118b209c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 170,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "13",
"filtername" : "price Range"
}
{
"_id" : ObjectId("558c108b209c033c947b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 182,
"weekofyear" : 24,
"productcount" : 1,
"count" : 24,
"position" : "2",
"productsclickedid" : "8156",
"sessionid" : "14",
"filtername" : "brand"
}
{
"_id" : ObjectId("558c108b909c033c947b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 188,
"weekofyear" : 24,
"productcount" : 1,
"count" : 24,
"position" : "2",
"productsclickedid" : "8156",
"sessionid" : "10",
"filtername" : "brand"
}
{
"_id" : ObjectId("558c108b209c033c937b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 162,
"weekofyear" : 24,
"productcount" : 1,
"count" : 24,
"position" : "autocomplete",
"productsclickedid" : "8156",
"sessionid" : "14",
"filtername" : "brand"
}
{
"_id" : ObjectId("558c101b209c033c937b0026"),
"term" : "aero",
"year" : 2014,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 363,
"weekofyear" : 24,
"productcount" : 0,
"count" : 24,
"position" : "autocomplete",
"productsclickedid" : "8156",
"sessionid" : "10",
"filtername" : "brand"
}
{
"_id" : ObjectId("558c101b299c033c937b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 183,
"weekofyear" : 24,
"productcount" : 0,
"count" : 24,
"position" : "2",
"productsclickedid" : "8156",
"sessionid" : "16",
"filtername" : "brand"
}
{
"_id" : ObjectId("558c101b199c033c937b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 183,
"weekofyear" : 24,
"productcount" : 0,
"count" : 24,
"position" : "autocomplete",
"productsclickedid" : "8156",
"sessionid" : "10",
"filtername" : "brand"
}
{
"_id" : ObjectId("558c101b199c033c939b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 185,
"weekofyear" : 24,
"productcount" : 0,
"count" : 24,
"position" : "autocomplete",
"productsclickedid" : "8156",
"sessionid" : "18",
"filtername" : "brand"
}
{
"_id" : ObjectId("558c101b199c044c937b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 163,
"weekofyear" : 24,
"productcount" : 0,
"count" : 24,
"position" : "2",
"productsclickedid" : "8156",
"sessionid" : "11",
"filtername" : "brand"
}
{
"_id" : ObjectId("558c191b199c033c939b0026"),
"term" : "aero",
"year" : 2014,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 178,
"weekofyear" : 24,
"productcount" : 0,
"count" : 24,
"position" : "autocomplete",
"productsclickedid" : "8156",
"sessionid" : "10",
"filtername" : "concern"
}
{
"_id" : ObjectId("558c198b909c033c947b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 188,
"weekofyear" : 24,
"productcount" : 1,
"count" : 24,
"position" : "2","
"productsclickedi"d" : "8156",
"sessionid" : "14"",
"filtername" : "c"oncern"
} "
"
"
{ "
"_id" : ObjectId(""558c118b310c022c947b0055"),
"term" : "aero st"orm tour",
"year" : 2015, "
"month" : 6, "
"day" : 1, "
"hour" : 17, "
"dayofyear" : 188,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "16",
"filtername" : "concern"
}
{
"_id" : ObjectId("539c118b310c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 4,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "18",
"filtername" : "concern"
}
{
"_id" : ObjectId("558c118b310c022c947b1145"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 4,
"weekofyear" : 22,
"productcount" : 0,
"count" : 21,
"position" : "autocomplete",
"productsclickedid" : "8156",
"sessionid" : "18",
"filtername" : "concern"
}
{
"_id" : ObjectId("558c992b310c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 1,
"weekofyear" : 22,
"productcount" : 0,
"count" : 2,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "10",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c118b123c022c947b0055"),
"term" : "aero storm tour",
"year" : 2014,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 364,
"weekofyear" : 22,
"productcount" : 0,
"count" : 32,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "17",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c223c310c022c947b0055"),
"term" : "aero storm tour",
"year" : 2014,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 365,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "14",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c220c310c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 196,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "10",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c108b209c311c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 195,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "10",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c999b209c311c947b0055"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 195,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "2",
"productsclickedid" : "8156",
"sessionid" : "19",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c999c310c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 196,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "14"
}
{
"_id" : ObjectId("558c220c310c022c749b0055"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 194,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "autocomplete",
"productsclickedid" : "8156",
"sessionid" : "15",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c730c310c022c749b0055"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 195,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "autocomplete",
"productsclickedid" : "8156",
"sessionid" : "10",
"filtername" : "Product Category"
}
I am trying to find the count of term and filtername in the specified date-range.
My query :
db.filterclick.aggregate(
{$match:
{$or :[
{ $and :[{dayofyear:{ $gte : 1, $lte : 4 }},{year : 2015}]},
{ $and :[{dayofyear:{ $gte : 363, $lte : 365 }},{year : 2014}]}
]
}
},
{$group:{
_id: "$term",
_id: "$filtername",
term :{$first :"$term"},
filtername :{$first : "$filtername"},
totalcount : {
$sum : "$count"
}
}
},
{
$sort : {totalcount : -1}
},{
$project: {
"term" : 1,
"filtername" : 1,
"totalcount" : 1,
"_id" : 0
}
}
)
Output :
{
"result" : [
{
"term" : "aero storm tour",
"filtername" : "Product Category",
"totalcount" : 56
},
{
"term" : "aero storm tour",
"filtername" : "concern",
"totalcount" : 43
},
{
"term" : "aero",
"filtername" : "brand",
"totalcount" : 24
}
],
"ok" : 1.0000000000000000
}
Desired Output :
{
"result" : [
{
"term" : "aero storm tour",
"filtername" : "Product Category",
"totalcount" : 4
},
{
"term" : "aero storm tour",
"filtername" : "concern",
"totalcount" : 44
},
{
"term" : "aero",
"filtername" : "brand",
"totalcount" : 24
},
{
"term" : "aero",
"filtername" : "concern",
"totalcount" : 21
}
],
"ok" : 1.0000000000000000
}
The query should aggregate and calculate the count based on combination on term and filtername , but it doesn't.
I know my query is not creating combination of filtername and term. How to do it?
Any suggestions ?
Upvotes: 0
Views: 69
Answers (1)
Blakes Seven
Reputation: 50406
Correct your $group. The _id needs to be a composite key. How you are writing it you are "overwriting" the value of _id so it only groups on $filtername:
{ "$group": {
"_id": { "term": "$term", "filtername": "$filtername" },
"term": { "$first":"$term" },
"filtername": { "$first": "$filtername" },
"totalcount" : { "$sum": "$count" }
}}
So _id is the "grouping key" and like all keys in BSON documents "there can only be one". That is why the totals are not coming out as the "combination" of those two fields in your grouping.
Upvotes: 1
Related Questions
- MongoDB aggregation group by query
- Mongo Aggregate group two field in a single query
- Group by 2 Fields as 1 Field
- How to group by two different fields
- MongoDb aggregate and group by two fields depending on values
- Mongo aggregate group by multiple values
- Aggregate with a Composite Key
- MongoDB aggregation with multiple keys
- Aggregation Grouping by Two Fields in MongoDB
- Group Multiple Values in Aggregation