CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
mfrachet
mfrachet

Reputation: 8922

Multiple aggregates in the same query

I have a problem that I dont know how to solve it with MongoDB syntax. In fact, this is my actual query : 

db.traitement".aggregate {$match: {timestampentre: {$gt: start}, timestampentre: {$lt: end}}}, {$project: {year: {$year: "$date_entre"}, month: {$month: "$date_entre"}, "carnetsanitairedone.isDoneDouche": "$carnetsanitairedone.isDoneDouche", "carnetsanitairedone.isDoneDetartrage": "$carnetsanitairedone.isDoneDetartrage"}}, {$group: {_id: {year: "$year", month: "$month", "carnetsanitairedone.isDoneDouche": "$carnetsanitairedone.isDoneDouche", "carnetsanitairedone.isDoneDetartrage": "$carnetsanitairedone.isDoneDetartrage"}, count: {$sum: 1}}}

that returns me this resultset :

[ { _id:
     { year: 2014,
       month: 10,
       'carnetsanitairedone.isDoneDouche': false,
       'carnetsanitairedone.isDoneDetartrage': false },
    count: 1 },
  { _id:
     { year: 2014,
       month: 10,
       'carnetsanitairedone.isDoneDouche': true,
       'carnetsanitairedone.isDoneDetartrage': true },
    count: 1 },
  { _id:
     { year: 1970,
       month: 1,
       'carnetsanitairedone.isDoneDouche': false,
       'carnetsanitairedone.isDoneDetartrage': false },
    count: 1 },
  { _id:
     { year: 1970,
       month: 1,
       'carnetsanitairedone.isDoneDouche': true,
       'carnetsanitairedone.isDoneDetartrage': true },
    count: 2 } ]

What I really need corresponds to the following resultset :

    'year': 2014,
    'month': 10,
    'count.isDoneDouche': 10,
    'count.isNotDoneDouche': 20,
    'count.isDoneDetartrage': 30,
    'count.isNotDoneDetartrage': 13

Can you help me with this request ?

Thanks for advance

Upvotes: 0

Views: 190

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151072

You can conditionally $sum items with the use of the $cond operator for any key that you supply as an _id value:

db.traitement.aggregate([ 
   { "$match": {
       "timestampentre": { "$gt": start, "$lt": end}
   }}, 
   { "$group": {
       "_id": {
           "year": { "$year": "$date_entre" },
           "month": { "$month": "$date_entre" }
       },
      "countIsDoneDouche": {
          "$sum": {
              "$cond": [
                  "$carnetsanitairedone.isDoneDouche",
                  1,
                  0
              ]
          }
      },
      "countIsNotDoneDouche": {
          "$sum": {
              "$cond": [
                  { "$ne": [ "$carnetsanitairedone.isDoneDouche", true ] },
                  1,
                  0
              ]
          }
      },
      "countIsDoneDetartrage": {
          "$sum": {
              "$cond": [
                  "$carnetsanitairedone.isDoneDetartrage",
                  1,
                  0
              ]
          }
      },
      "countIsNotDoneDetartrage": {
          "$sum": {
              "$cond": [
                  { "$ne": [ "$carnetsanitairedone.isDoneDetartrage", true ] }
                  1,
                  0
              ]
          }
      }
  }}
])

This allows the conditions of the supplied "ternary" in each $cond operation to determine whether the "counter" is incremented for the current value or not.

Upvotes: 1

Related Questions