MongoDB double $group aggregation by date and status

Question

I have several documents that looks like this (minus many other irrelevant fields):

  [{
    status: 'open',
    createdDate: 2021-06-17T09:02:58.325Z
  },
  {
    status: 'declined',
    createdDate: 2021-07-25T09:09:15.851Z
  },
  {
    status: 'declined',
    createdDate: 2021-09-22T09:32:14.958Z
  },
  {
    status: 'open',
    createdDate: 2021-09-02T09:45:26.584Z
  },
  {
    status: 'referral',
    createdDate: 2021-09-05T09:46:02.764Z
  }]

For this subgroup of the collection I want to aggregate the next result:

{
    "2021-06" : { submitted: 1, referral: 0, declined: 0},
    "2021-07" : { submitted: 1, referral: 0, declined: 1},
    "2021-08" : { submitted: 0, referral: 0, declined: 0},
    "2021-09" : { submitted: 3, referral: 1, declined: 1},
}

Submitted are the total documents (open, referral and declined). I tried using $group in a couple of ways but it didn't work out. Any suggestions? Thanks!

Answer 1

Query

• group by the date as string with only the year and month
• count 3 accumulators, the first always adds, the second and third add only if they see status open(the second) and status(declined) the third
• replace root and array to object, to make the data key, and the data as nested document, like in your expected output

Test code here

aggregate(
[{"$group":
  {"_id":{"$dateToString":{"date":"$createdDate", "format":"%Y-%m"}},
   "submitted":{"$sum":1},
   "referral":{"$sum":{"$cond":[{"$eq":["$status", "open"]}, 1, 0]}},
   "declined":
   {"$sum":{"$cond":[{"$eq":["$status", "declined"]}, 1, 0]}}}},
 {"$replaceRoot":
  {"newRoot":
   {"$arrayToObject":
    [[{"k":"$_id",
       "v":
       {"submitted":"$submitted",
        "referral":"$referral",
        "declined":"$declined"}}]]}}}])