aggregation framework - Mongodb find distinct array fields and count them -
i have collection documents looks like:
{ "_id" : objectid("560ba1e86db58e34168b456a"), "object_id" : "1722224", "user" : { "id" : null, "cookie" : "senal9ddphytdmxyu3e23g" }, "createddate" : isodate("2015-09-30t11:48:40.000+03:00"), "activity" : [ { "name" : "map click", "selector" : "#tomap", "event" : "click", "time" : isodate("2015-09-30t12:30:59.000+03:00") }, { "name" : "show contacts (main block)", "selector" : "#jsn-showcontacts", "event" : "click", "time" : isodate("2015-09-30t11:48:47.687+03:00") }, { "name" : "show contacts (main block)", "selector" : "#jsn-showcontacts", "event" : "click", "time" : isodate("2015-09-30t15:34:12.000+03:00") } ] } i need pass mongodb range of dates , day-by-day result contains: 1. date 2. set of selectors clicked date , count of clicks:
{ "date" : isodate("2015-09-29t00:00:00.000+03:00"), "selectors" : [ {"#tomap" : 100}, {"#jsn-showcontacts" : 200} ] }, { "date" : isodate("2015-09-30t00:00:00.000+03:00"), "selectors" : [ {"#tomap" : 50}, {"#jsn-showcontacts" : 80}, {"#toorder" : 10} ] } the result should count click once every document, example if have 2 #showcontacts-min clicks in 1 document:
"activity" : [ { "name" : "show contacts (main block)", "selector" : "#jsn-showcontacts", "event" : "click", "time" : isodate("2015-09-30t11:48:47.687+03:00") }, { "name" : "show contacts (main block)", "selector" : "#jsn-showcontacts", "event" : "click", "time" : isodate("2015-09-30t15:34:12.000+03:00") } ] it have couned once.
is possible on db side 1 query or should implemented on application side multiple db queries?
it possible desired result using single query.i'm assuming "selector" values prior know can hard coded in mongodb query.
here have created sample documents per schema :
> db.a.find().pretty() { "_id" : objectid("560ba1e86db58e34168b456a"), "object_id" : "1722224", "user" : { "id" : null, "cookie" : "senal9ddphytdmxyu3e23g" }, "createddate" : isodate("2015-09-30t08:48:40z"), "activity" : [ { "name" : "map click", "selector" : "#tomap", "event" : "click", "time" : isodate("2015-09-30t09:30:59z") }, { "name" : "show contacts (main block)", "selector" : "#jsn-showcontacts", "event" : "click", "time" : isodate("2015-09-30t08:48:47.687z") }, { "name" : "show contacts (main block)", "selector" : "#jsn-showcontacts", "event" : "click", "time" : isodate("2015-09-30t12:34:12z") } ] } { "_id" : objectid("560c19550e45be0a683ccd01"), "object_id" : "1722224", "user" : { "id" : null, "cookie" : "senal9ddphytdmxyu3e23g" }, "createddate" : isodate("2015-10-30t08:48:40z"), "activity" : [ { "name" : "map click", "selector" : "#tomap", "event" : "click", "time" : isodate("2015-09-30t09:30:59z") }, { "name" : "show contacts (main block)", "selector" : "#jsn-showcontacts", "event" : "click", "time" : isodate("2015-09-30t08:48:47.687z") }, { "name" : "show contacts (main block)", "selector" : "#jsn-showcontacts", "event" : "click", "time" : isodate("2015-09-30t12:34:12z") } ] } { "_id" : objectid("560c19750e45be0a683ccd02"), "object_id" : "1722224", "user" : { "id" : null, "cookie" : "senal9ddphytdmxyu3e23g" }, "createddate" : isodate("2015-10-30t08:48:40z"), "activity" : [ { "name" : "map click", "selector" : "#tomap", "event" : "click", "time" : isodate("2015-09-30t09:30:59z") }, { "name" : "show contacts (main block)", "selector" : "#jsn-showcontacts", "event" : "click", "time" : isodate("2015-09-30t08:48:47.687z") }, { "name" : "show contacts (main block)", "selector" : "#jsn-showcontacts", "event" : "click", "time" : isodate("2015-09-30t12:34:12z") } ] } { "_id" : objectid("560c19940e45be0a683ccd03"), "object_id" : "1722224", "user" : { "id" : null, "cookie" : "senal9ddphytdmxyu3e23g" }, "createddate" : isodate("2016-01-30t08:48:40z"), "activity" : [ { "name" : "map click", "selector" : "#tomap", "event" : "click", "time" : isodate("2015-09-30t09:30:59z") }, { "name" : "show contacts (main block)", "selector" : "#jsn-showcontacts", "event" : "click", "time" : isodate("2015-09-30t08:48:47.687z") }, { "name" : "show contacts (main block)", "selector" : "#jsn-showcontacts", "event" : "click", "time" : isodate("2015-09-30t12:34:12z") } ] } below query give result.here count selected without considering duplicate values within single document
>db.a.aggregate([{"$match": {createddate:{"$gt": isodate("2014-10-30t11:48:40.000+03:00"), "$lt":isodate("2016-10-30t11:48:40.000+03:00")}}}, {'$group':{"_id":{"date":"$createddate"}, "#tomap":{"$sum":{"$cond":{"if":{"$anyelementtrue":{ "$map":{"input":"$activity", "as":"act", "in":{"$eq": ["$$act.selector","#tomap"]}}}},"then":1,"else":0}}}, "#jsn-showcontacts":{"$sum":{"$cond":{"if":{"$anyelementtrue":{ "$map":{ "input":"$activity", "as":"act", "in":{"$eq":["$$act.selector","#jsn- showcontacts"]}}}},"then":1,"else":0}}}}}]) //result: { "_id" : { "date" : isodate("2016-01-30t08:48:40z") }, "#tomap" : 1, "#jsn-showcontacts" : 1 } { "_id" : { "date" : isodate("2015-10-30t08:48:40z") }, "#tomap" : 2, "#jsn-showcontacts" : 2 } { "_id" : { "date" : isodate("2015-09-30t08:48:40z") }, "#tomap" : 1, "#jsn-showcontacts" : 1 } selectors not in array in result,but hope do.you can use $project last pipelie stage modify result schema
Comments
Post a Comment