Multiple conditional sums in mongodb aggregation

Question

I'm trying to return the total of requests by type based on their status:

• If there is no status set, the request should be added to requested
• If the status is ordered, the request should be added to ordered

• If the status is arrived, the request should be added to arrived

  caseRequest.aggregate([{
    $group: {
      _id: "$product",
      suggested: {
        $sum: {
            $cond: [{
              $ifNull: ["$status", true]
            },
            1, 0
        ]}
      },
      ordered: {
        $sum: {
          $cond: [{
                $eq: ["$status", "ordered"]
            },
            1, 0
          ]
        }
      },
      arrived: {
        $sum: {
          $cond: [{
               $eq: ["$status", "arrived"]
            },
            1, 0
          ]
        }
      }
    }
  }
  

But for some reason it doesn't find any request status ordered or arrived. If in the database I have 48 requests, 45 of them without status, 2 with ordered and 1 with arrived, it returns:

[
    {
        _id: "xxx",
        suggested: 48,
        ordered: 0,
        arrived: 0,
    },
    ...
]

Answer 1

Try this approach,

Return the total number of requests by type based on their status

Now the simplest way to get the count of different status is to use aggregate pipeline with $group on the status field

db.stackoverflow.aggregate([{ $group: {_id: "$status", count: {$sum:1}} }])

We will be getting a result similar to this

{ "_id" : "", "count" : 2 }
{ "_id" : "arrived", "count" : 3 }
{ "_id" : "ordered", "count" : 4 }

The schema which is used to retrieve these records is very simple so that it will be easier to understand. The schema will have a parameter on the top level of the document and the value of status can be "ordered", "arrived" or empty

Schema

{ "_id" : ObjectId("5798c348d345404e7f9e0ced"), "status" : "ordered" }

The collection is populated with 9 records, with status as ordered, arrived and empty

db.stackoverflow.find()
{ "_id" : ObjectId("5798c348d345404e7f9e0ced"), "status" : "ordered" }
{ "_id" : ObjectId("5798c349d345404e7f9e0cee"), "status" : "ordered" }
{ "_id" : ObjectId("5798c34ad345404e7f9e0cef"), "status" : "ordered" }
{ "_id" : ObjectId("5798c356d345404e7f9e0cf0"), "status" : "arrived" }
{ "_id" : ObjectId("5798c357d345404e7f9e0cf1"), "status" : "arrived" }
{ "_id" : ObjectId("5798c358d345404e7f9e0cf2"), "status" : "arrived" }
{ "_id" : ObjectId("5798c35ad345404e7f9e0cf3"), "status" : "ordered" }
{ "_id" : ObjectId("5798c361d345404e7f9e0cf4"), "status" : "" }
{ "_id" : ObjectId("5798c362d345404e7f9e0cf5"), "status" : "" }

db.stackoverflow.count()
9

Hope it Helps!!