How to get aggregate value by filtering inside of a document by a given parameter

Question

I have a collection like the following dataset, which is named useragents.

I have a use case of finding the sum amount of the value in each useagents. In this case, as an example I use useragents as Linux and Ubuntu OS. It can be dyanamic. As my first step I find the solutions to get aggreagate sum value of each useragents using aggregation framework.

Please refer this background question.

But I want to get aggregate the value by checking each venuelist, ssidlist, maclist according the the given parameter list. It is a very hard problem to me as sometimes my data structure can be complex.

I want to get aggreagate sum amount of each useragents(linux, ubuntu) given the following parameters:

parameterlist 1

    venueid :: [VID001, VID002]  // this is compulsory field in parameter list

    ssids : [SSID001]            // this is optional filed in parameter list
    mac : [22:22:22:22:22:22]


    output

    linux: 12 + 2 = 14
    ubuntu : 2 + 5 = 7

parameterlist 2

    venueid :: [VID001, VID002] // this is compulsory field in parameter list

    mac : [22:22:22:22:22:22]   // this is optional filed in parameter list

    output

    linux: 12 + 4 + 2 = 16
    ubuntu : 2 + 2 + 5 = 7

This is sample data set

{
        "_id" : ObjectId("57f940c4932a00aba387b0b0"),
        "tenantID" : 1,
        "date" : "2016-10-09 00:23:56",
        "venueList" : [
            {
                "id" : “VID001”,
                "sum" : [
                    {
                          "name" : "linux",
                          "value" : 16
                    },
                    {
                        "name" : "ubuntu",
                        "value" : 7
                    }
                ],
                “ssidList” : [    // this is list of ssid’s in venue
                    {
                        "id" : “SSID001”,
                        "sum" : [
                            {
                                "name" : "linux",
                                "value" : 12
                            },
                            {
                                "name" : "ubuntu",
                                "value" : 2
                            }
                        ],
                        “macList” : [  // this is mac list inside particular ssid  ex: this is mac list inside the SSID1212
                            {
                                "id" : “22:22:22:22:22:22”,
                                "sum" : [
                                    {
                                        "name" : "linux",
                                        "value" : 12
                                    },
                                    {
                                        "name" : "ubuntu",
                                        "value" : 2
                                    }
                                ]
                            }
                        ]
                    },
                    {
                        "id" : “SSID002”,
                        "sum" : [
                            {
                                "name" : "linux",
                                "value" : 4
                            },
                            {
                                "name" : "ubuntu",
                                "value" : 5
                            }
                        ],
                        “macList” : [  // this is mac list inside particular ssid  ex: this is mac list inside the SSID1212
                            {
                                "id" : “22:22:22:22:22:22”,    // this should be select in parameterlist 02 because there is no ssid selection in parameter list.
                                "sum" : [
                                    {
                                        "name" : "linux",
                                        "value" : 4
                                    },
                                    {
                                        "name" : "ubuntu",
                                        "value" : 2
                                    }
                                ]
                            },
                             {
                                "id" : “44:44:44:44:44:44”,
                                "sum" : [
                                    {
                                        "name" : "linux",
                                        "value" : 12
                                    },
                                    {
                                        "name" : "ubuntu",
                                        "value" : 3
                                    }
                                ]
                            }
                        ]
                    }
                ]
            },
            {
                "id" : “VID002”,
                "sum" : [
                    "sum" : [
                        {
                            "name" : "linux",
                            "value" : 2
                        },
                        {
                            "name" : "linux",
                            "value" : 5
                        }

                    ],
                ],
                "ssidList" : [
                    {
                        "id" : “SSID001”,
                        "sum" : [
                            {
                                "name" : "linux",
                                "value" : 2
                            },
                            {
                                "name" : "linux",
                                "value" : 5
                            }

                        ],
                        "macList" : [
                            {
                                "id" : “22:22:22:22:22:22”,
                                "sum" : [
                                    {
                                        "name" : "linux",
                                        "value" : 2
                                    }
                                    {
                                        "name" : "linux",
                                        "value" : 5
                                    }
                                ]
                            }
                        ]
                    }
                ]
            }
        ]
    }

Please help me solve this problem, I will appreciate that. If there is any problem in my dataset as well please mention it. Your comments are of more help to me since I'm a fresher in MongoDB.

hope this one will give you a guidance http://stackoverflow.com/questions/14568283/mongodb-aggregation-count-array-set-size — Anushka Ekanayake, Oct 14 '16 at 10:55
thank you for responsing. but I am problem with how filter result set in multiple parameter inside another parameter. — Sandun Priyanka, Oct 15 '16 at 05:35
In this case, want a nested query mechanism for filter result.I have problem with how to generate query in mongodb — Sandun Priyanka, Oct 15 '16 at 05:37

Lakmal Vithanage · Accepted Answer · 2016-10-29T10:09:59.417

The sample query as follows,

if the method structure like this, getTotal(list venueIds, list ssids , list macs)

 if macs!= empty && ssids != empty && venueIds != empty


 db.getCollection('ua').aggregate(
[

{$match:{"venueList":{ $elemMatch : { id :{$in: venueIds }}},
  "venueList.ssidList":{ $elemMatch : { id :{$in: ssids }}},
  "venueList.ssidList.macList":{ $elemMatch : { id :{$in: macs }}}
}},
{ $unwind : "$venueList" },
{ $project : { "ssidList" : "$venueList.ssidList"} },
{ $unwind : "$ssidList" },
{ $project : { "macList" : "$ssidList.macList"} },
{ $unwind : "$macList" },
{ $project : { "sum" : "$macList.sum"} },
{ $unwind : "$sum" },

 {
       $group:
         {
           _id: "$sum.name",
           total: { $sum: "$sum.value" }          
         }
     }

]
)

 if macs == empty && ssids != empty && venueIds != empty

db.getCollection('ua').aggregate(
[

{$match:{"venueList":{ $elemMatch : { id :{$in:venueIds}}},
  "venueList.ssidList":{ $elemMatch : { id :{$in:ssids}}}
}},
{ $unwind : "$venueList" },
{ $project : { "ssidList" : "$venueList.ssidList"} },
{ $unwind : "$ssidList" },
{ $project : { "sum" : "$ssidList.sum"} },
{ $unwind : "$sum" },
 {
       $group:
         {
           _id: "$sum.name",
           total: { $sum: "$sum.value" }          
         }
     }

]
)


 if macs == empty && ssids = empty && venueIds != empty

db.getCollection('ua').aggregate(
[

{$match:{"venueList":{ $elemMatch : { id :{$in: venueIds}}}
}},
{ $unwind : "$venueList" },
{ $project : { "sum" : "$venueList.sum"} },
{ $unwind : "$sum" },
 {
       $group:
         {
           _id: "$sum.name",
           total: { $sum: "$sum.value" }          
         }
     }
]
)

If there are so many documents to be processed, you can use allowDiskUse for that.

How to get aggregate value by filtering inside of a document by a given parameter

1 Answers1