i have 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 trying find count of term , filtername in 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 , calculate count based on combination on term , filtername , doesn't.
i know query not creating combination of filtername , term. how it?
any suggestions ?
correct $group. _id needs composite key. how writing "overwriting" value of _id groups on $filtername:
{ "$group": { "_id": { "term": "$term", "filtername": "$filtername" }, "term": { "$first":"$term" }, "filtername": { "$first": "$filtername" }, "totalcount" : { "$sum": "$count" } }} so _id "grouping key" , keys in bson documents "there can one". why totals not coming out "combination" of 2 fields in grouping.
Comments
Post a Comment