CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
JSBach
JSBach

Reputation: 4747

How do I create nested aggregations with count on MongoDB?

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

Upvotes: 1

Views: 89

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151082

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
}

Upvotes: 1

Related Questions