MongoDB group aggregation with condition in $sum

user_5951462 0
Viewed 174

Collection of my database is something like below

[{ 
   _id:1,
   status:"active",
   sale: 4,
   createdAt:"2019-10-08 08:46:19"
},
{
   _id:2,
   status:"inactive",
   sale:5,
   createdAt:"2019-10-08 06:41:19"
},
{
   _id:2,
   status:"inactive",
   sale:5,
   createdAt:"2019-10-08 02:01:19"
}]

I need to group it by "day".The result which I want

[{
  createdAt:"2019-10-08 02:01:19",
  inactive: 2,
  active:1,
  salesOfActive: 4,
  salesOfInactive:10
}]

I am not getting the actual result which I want any help will be highly appreciated

I had try with this but won't get an idea how i will get salesOfActive and salesOfInactive per day

{
        $group: {
          _id: {
            day: { $dayOfMonth: "$createdAt" }
          },
          inActive:{$sum: { status:"inactive"}},
          active:{$sum: { status:"active"}},
          salesOfActive: { $sum:$sale  }
        }
}
mongoose aggregation-framework mongodb-query mongodb javascript
1 Answers

Basically you need to $sum each field $conditionally here

db.collection.aggregate([
  { "$group": {
    "_id": {
      "$dayOfMonth": { "$dateFromString": { "dateString": "$createdAt" } }
    },
    "inactive": {
      "$sum": { "$cond": [{ "$eq": ["$status", "inactive"] }, 1, 0] }
    },
    "active": {
      "$sum": { "$cond": [{ "$eq": ["$status", "inactive"] }, 0, 1] }
    },
    "salesOfInactive": {
      "$sum": { "$cond": [{ "$eq": ["$status", "inactive"] }, "$sale", 0] }
    },
    "salesOfActive": {
      "$sum": { "$cond": [{ "$eq": ["$status", "inactive"] }, 0, "$sale"] }
    }
  }}
])

MongoPlayground

user_75106570
Related