How to use multiple $sums in MongoDB aggregation?

Question

MongoDB

Hello.
Collection example:

{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2,  "status": "ok"}
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "status": "ok"}
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5,  "status": "ok"}
{ "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 10,  "status": "ok"}
{ "_id" : 5, "item" : "xyz", "price" : 5, "quantity" : 10,  "status": "error"}

How to group by "item" in the resulting document, count the number of these matches, also group by "status" and also count these fields? To make it look like this:

{ "_id" : {{ "item" : "abc", count: 2 }, {"status":[ "error": 0, "ok": 2 ]}}
{ "_id" : {{ "item" : "jkl", count: 1 }, {"status":[ "error": 0, "ok": 1 ]}}
{ "_id" : {{ "item" : "xyz", count: 2 }, {"status":[ "error": 1, "ok": 1 ]}}

If you group separately for each field, you can calculate everything that is needed. But how to make within one request - I do not understand. Is it possible?

Answer 1

In general, it turned out like this (logic - we go from the lowest nesting level, count it, push it to the next level, count, etc.):

db.collection.aggregate({
  "$group": {
    _id: {
      item: "$item",
      status: "$status"
    },
    count: {
      $sum: 1
    }
  }
},
{
  "$group": {
    _id: {
      item: "$_id.item"
    },
    count: {
      $sum: "$count"
    },
    status: {
      "$push": {
        status: "$_id.status",
        count: "$count"
      }
    }
  }
})