How do I create nested aggregations with count on MongoDB?

Question

I am learning MongoDB in order to see if it matches our needs. Currently we use heavily aggregations, so I am testing the flexibility of the Aggregation Framework. I started with this hierarchy

db.companytest3.insert({"name":"A", age:7})

db.companytest3.insert({"name":"B", age:17, owner:"A"})
db.companytest3.insert({"name":"C", age:12, owner:"A"})
db.companytest3.insert({"name":"D", age:7, owner:"B"})
db.companytest3.insert({"name":"E", age:13, owner:"B"})
db.companytest3.insert({"name":"F", age:23, owner:"C"})

So I have:

db.companytest3.find()
{ "_id" : ObjectId("5457c2c0fa82c305e0b80006"), "name" : "A", "age" : 7 }
{ "_id" : ObjectId("5457c2cafa82c305e0b80007"), "name" : "A", "age" : 7 }
{ "_id" : ObjectId("5457c2d0fa82c305e0b80008"), "name" : "B", "age" : 17, "owner" : "A" }
{ "_id" : ObjectId("5457c2d6fa82c305e0b80009"), "name" : "C", "age" : 12, "owner" : "A" }
{ "_id" : ObjectId("5457c2ddfa82c305e0b8000a"), "name" : "D", "age" : 7, "owner" : "B" }
{ "_id" : ObjectId("5457c2e4fa82c305e0b8000b"), "name" : "E", "age" : 13, "owner" : "B" }
{ "_id" : ObjectId("5457c2eafa82c305e0b8000c"), "name" : "F", "age" : 23, "owner" : "C" }

My goal is to aggregate the children using their ages, so I have something like this:

{
  "_id" : null,
  "children" : [
      {
        "range:" : "lower than 10",
        total: 1,
        names: ["A"]
      }
      {
        "range:" : "higher than 10",
        total: 0,
        names: []
      }
    ],
  "total" : 1
}
{
  "_id" : "A",
  "children" : [
      {
        "range:" : "lower than 10",
        total: 0,
        names: []
      }
      {
        "range:" : "higher than 10",
        total: 2,
        names: ["C","B"]
      }
    ],
  "total" : 1
}
{
  "_id" : "B",
  "children" : [
      {
        "range:" : "lower than 10",
        total: 1,
        names: ["D"]
      }
      {
        "range:" : "higher than 10",
        total: 13,
        names: ["E"]
      }
    ],
  "total" : 1
}
{
  "_id" : "C",
  "children" : [
      {
        "range:" : "lower than 10",
        total: 0,
        names: []
      }
      {
        "range:" : "higher than 10",
        total: 1,
        names: ["F"]
      }
    ],
  "total" : 1
}

I feel I am getting near, I've got this query:

db.companytest3.aggregate(
{ $project: {
    "_id": 0,
    "range": {
      $concat: [{
        $cond: [ { $lte: ["$age", 10] }, "até 10", "" ]
      }, {
        $cond: [ { $gte: ["$age", 11] }, "mais de 10", "" ]
      }]
    },
    "owner": "$owner",
    "name" : "$name"
  }
},
   { 
    $group: { 
      _id:   { owner: "$owner", range: "$range" }, 
      children: { $addToSet: { name: "$name", range: "$range"} } ,
      total: { $sum: 1} 
    } 
},
   { 
    $group: { 
      _id:   { owner:"$_id.owner" },
      children: { $addToSet: "$children" }
    } 
}
)

which gives me the following output:

{ "_id" : { "owner" : null }, "children" : [ [ { "name" : "A", "range" : "até 10" } ] ] }
{ "_id" : { "owner" : "A" }, "children" : [ [ { "name" : "C", "range" : "mais de 10" }, { "name" : "B", "range" : "mais de 10" } ] ] }
{ "_id" : { "owner" : "B" }, "children" : [ [ { "name" : "D", "range" : "até 10" } ], [ { "name" : "E", "range" : "mais de 10" } ] ] }
{ "_id" : { "owner" : "C" }, "children" : [ [ { "name" : "F", "range" : "mais de 10" } ] ] }

Now I am having issues to group the items by owner and keep sum the total, I am stuck and I do not know how to proceed. I've been trying many diferent alternatives using groups variations but I do not feel they are worth posting here.

How can I change my current query so I group the children by range and add the count?

thanks! :D

Neil Lunn · Accepted Answer · 2014-11-04 01:24:59Z

It should be possible in earlier versions, but even basically looking at how you want to manipulate the result, the simplest way I can see is with the help of some operators introduced in MongoDB 2.6.

db.companytest3.aggregate([
  { "$group": {
    "_id": "$owner",
    "lowerThanTenNames": {
      "$addToSet": {
        "$cond": [
          { "$lte": [ "$age", 10 ] },
          "$name",
          false
        ]
      }
    },
    "lowerThanTenTotal": { 
      "$sum": { 
        "$cond": [
          { "$lte": [ "$age", 10 ] },
          1,
          0
        ]
      }
    },
    "moreThanTenNames": {
      "$addToSet": {
        "$cond": [
          { "$gte": [ "$age", 11 ] },
          "$name",
          false
        ]
      }
    },
    "moreThanTenTotal": {
      "$sum": { 
        "$cond": [
          { "$gte": [ "$age", 11 ] },
          1,
          0
        ]
      }
    }
  }},
  { "$project": {
    "children": {
      "$map": {
        "input": { "$literal": ["L", "M"] },
        "as": "el",
        "in": {
          "$cond": [
            { "$eq": [ "$$el", "L" ] },
            {
              "range": { "$literal": "lower than 10" },
              "total": "$lowerThanTenTotal",
              "names": {
                "$setDifference": [
                  "$lowerThanTenNames",
                  [false]
                ]
              }
            },
            {
              "range": { "$literal": "higher than 10" },
              "total": "$moreThanTenTotal",
              "names": {
                "$setDifference": [
                  "$moreThanTenNames",
                  [false]
                ]
              }
            }
          ]
        }
      }
    },
    "total": { "$add": [ "$lowerThanTenTotal",  "$moreThanTenTotal" ]},
  }},
  { "$sort": { "_id": 1 } }
])

Basically you want to separate these out into two sets of results for each grouping, being one for each age range. Due to the use of conditional operators, the "names" sets then need to be filtered for any false values where the conditions did not match.

The other thing that needs to be done is to coerce these results from separate fields into an array. The $map operator makes this simple by just providing a two element template with effectively "A/B" choices to do the re-mapping.

Since we had discrete fields here before they were re-mapped onto an array, you can just supply each "total" field as an argument to $add in order to get the combined total.

Produces exactly this:

{
    "_id" : null,
    "children" : [
        {
            "range" : "lower than 10",
            "total" : 1,
            "names" : ["A"]
        },
        {
            "range" : "higher than 10",
            "total" : 0,
            "names" : [ ]
        }
    ],
    "total" : 1
}
{
    "_id" : "A",
    "children" : [
        {
            "range" : "lower than 10",
            "total" : 0,
            "names" : [ ]
        },
        {
            "range" : "higher than 10",
            "total" : 2,
            "names" : ["C","B"]
        }
    ],
    "total" : 2
}
{
    "_id" : "B",
    "children" : [
        {
            "range" : "lower than 10",
            "total" : 1,
            "names" : ["D"]
        },
        {
            "range" : "higher than 10",
            "total" : 1,
            "names" : ["E"]
        }
    ],
    "total" : 2
}
{
    "_id" : "C",
    "children" : [
        {
            "range" : "lower than 10",
            "total" : 0,
            "names" : [ ]
        },
        {
            "range" : "higher than 10",
            "total" : 1,
            "names" : ["F"]
        }
    ],
    "total" : 1
}

Thanks a lot! It was much more complex than I though, I am really starting with Mongo. I went over your answer and recreated here as an exercise and I understood almost everything. I am just a little bit confused with this part ""input": { "$literal": ["L", "M"] }, "as": "el", "in": { "$cond": [ { "$eq": [ "$$el", "L" ] }," I understand that "as" defined the variable names for the items (so they can be used as $$el). I do not get the input and the $cond part. Could you please explain?
@Oscar These are the arguments to the $map operator where it is being fed a two element array from the $literal declaration. The $cond is a ternary operator, that tests the value returned from that element and where the condition matches the second argument is returned, otherwise the third is returned. Basically "if..then..else". So this part makes the "children" array in the results. Just by substituting values. So swapping out "L" and "M" for the values from the appropriate fields.
Cool, thanks a lot! I would give you more points if I had any way to do so :)

Collectives™ on Stack Overflow

How do I create nested aggregations with count on MongoDB?

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related