Mongodb find distinct array fields and count them

Question

I have a 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 to pass to mongodb range of dates and get a day-by-day result that contains: 1. Date 2. Set of selectors clicked that date and count of that 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 for every document, for example if i have two #showContacts-Min clicks in one 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 to be couned once.

Is it really possible to do on DB side as one query or should it be implemented on application side with multiple db queries?

faisal_kk · Accepted Answer · 2015-09-30 17:48:59Z

It is well possible to get desired result using single query.I'm assuming your "selector" values are prior know so that it can be hard coded in MongoDB query.

Here I have created sample documents as per your 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 will give your result.Here Count is selected without considering duplicate values within a 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 are not in an array in Result,But I hope this will do.You can use $project as last pipelie stage to modify result schema

Thanks for your answer but the point is that i don't want move DB logic to application level. Set of selectors is not fixed, it changes in time. Sure i can try firstly get an array of selectors in my applicaton and then make a second query, but if it is possible to program all logic in DB side i prefer to do it.
I got your point ,But dynamic key names can't be generated in MongoDB aggregation query as of now.Best practical approach will be offload data intensive works to MongoDB as much as possible.Rest of logic place in application.I don't think you can all program logic to DB.Actually MongoDB don't recommend that.
Thank you. I splitted my query into two. First part gets distinct list of available selectors, then application generate second query based on that list using the way listed in your answer.

Collectives™ on Stack Overflow

Mongodb find distinct array fields and count them

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related