CODEWITHSUNDEEP
mongodbmongooseaggregation-framework
matskiv
matskiv

Reputation: 401

MongoDB aggregate with dual sum

I have collection of mobile devices, and i want to count devices for each manufacturer and also count each model. All i could get so far is:

{ $group : { 
  _id : "$hw.man", 
  mantotal : { $sum : 1 },
  models : { $addToSet : "$hw.mod" } 
} }

result:
[{"_id":"LGE","mantotal":1,"models":["GT540"]},{"_id":"asus","mantotal":1,"models":["Nexus 7"]},{"_id":"samsung","mantotal":3,"models":["GT-I9300","GT-I9200"]}]

or

{ $group : { 
  _id : { man : "$hw.man", mod : "$hw.mod" }, 
  total : { $sum : 1 } }
}

result:
[{"_id":{"man":"LGE","mod":"GT540"},"total":1},{"_id":{"man":"asus","mod":"Nexus 7"},"total":1},{"_id":{"man":"samsung","mod":"GT-I9300"},"total":2},{"_id":{"man":"samsung","mod":"GT-I9200"},"total":1}]

How can I achieve result like this:

{"_id":"samsung","mantotal":3,"models":[{mod: "GT-I9300", modtotal: 2}, {mod: "GT-I9200", modtotal: 1}]}

Upvotes: 1

Views: 396

Answers (1)

Asya Kamsky
Asya Kamsky

Reputation: 42352

Assuming you have a simple collection of documents representing a phone, listing its manufacturer and model, like this:

> db.phones.find({},{_id:0})
{ "man" : "LG", "mod" : "GT540" }
{ "man" : "LG", "mod" : "AB123" }
{ "man" : "Apple", "mod" : "iPhone4" }
{ "man" : "Apple", "mod" : "iPhone5" }
{ "man" : "Apple", "mod" : "iPhone5" }
{ "man" : "LG", "mod" : "GT540" }
{ "man" : "LG", "mod" : "GT540" }
{ "man" : "Samsung", "mod" : "Galaxy" }

Here is how you would group multiple times to get the total and subtotals by manufacturer and model number:

> gg1 =
{
 "$group" : {
    "_id" : {
        "ma" : "$man",
        "mo" : "$mod"
    },
    "subTotals" : {
        "$sum" : 1
    }
 }
}
> gg2 =
{
 "$group" : {
    "_id" : "$_id.ma",
    "total" : {
        "$sum" : "$subTotals"
    },
    "models" : {
        "$push" : {
            "mod" : "$_id.mo",
            "sub" : "$subTotals"
        }
    }
 }
}

> db.phones.aggregate(gg1, gg2)
{
"result" : [
    {
        "_id" : "LG",
        "total" : 4,
        "models" : [
            {
                "mod" : "AB123",
                "sub" : 1
            },
            {
                "mod" : "GT540",
                "sub" : 3
            }
        ]
    },
    {
        "_id" : "Apple",
        "total" : 3,
        "models" : [
            {
                "mod" : "iPhone5",
                "sub" : 2
            },
            {
                "mod" : "iPhone4",
                "sub" : 1
            }
        ]
    },
    {
        "_id" : "Samsung",
        "total" : 1,
        "models" : [
            {
                "mod" : "Galaxy",
                "sub" : 1
            }
        ]
    }
],
"ok" : 1
 }

Upvotes: 2

Related Questions