MongoDB Aggregation Group Arrays By Date

Question

I have this data set (two object arrays):

_id:"PBea1d7ca5-5865-493b-bf05-40a9665de00a"
checkouts:Array
    0:Object
        date:2018-11-23 17:29:49.483
        id:"CICOf3195baf-49b4-484e-ab39-03ab1f79ca04"
waitlist:Array
    0:Object
        date:2018-11-05 21:17:02.407
        id:"WTL56ab268d-2929-493e-967b-5606d9473c18"
    1:Object
        date:2018-12-11 20:50:54.669
        id:"WTL53bc7e6a-4f29-421e-a27a-37b1c1b28735"
    2:Object
        date:2018-12-14 20:25:17.451
        id:"WTLd2efa095-cfe0-4e5e-b8bd-c369acd45306"

I need to group array data by month and year, where the result will be two documents for the months in the year

_id:Object
    id:"PBea1d7ca5-5865-493b-bf05-40a9665de00a"
    month:11
    year:2018
checkouts:1
waitlist:1

_id:Object
    id:"PBea1d7ca5-5865-493b-bf05-40a9665de00a"
    month:12
    year:2018
checkouts:0
waitlist:2

I have tried to ($unwind and then $group) each array field, but it ends up creating duplicate entries, and the sums become equal.

the _id field needs to be the year and month:

_id: {id:'$_id', month:{$month:'$groupeddata.date'}, year:{$year:'$groupeddata.date'}}, Any pointers would help.

Answer 1

You can use below aggregation

db.collection.aggregate([
  { "$facet": {
    "checkouts": [
      { "$unwind": "$checkouts" },
      { "$group": {
        "_id": {
          "_id": "$_id",
          "date": { "$dateToString": { "date": "$waitlist.date", "format": "%Y-%m" }}
          }
        },
        "checkouts": { "$sum": 1 }
      }},
      { "$replaceRoot": {
        "newRoot": {
          "$mergeObjects": ["$$ROOT", "$_id"]
        }
      }}
    ],
    "waitlist": [
      { "$unwind": "$waitlist" },
      { "$group": {
        "_id": {
          "_id": "$_id",
          "date": { "$dateToString": { "date": "$waitlist.date", "format": "%Y-%m" }}
        },
        "waitlist": { "$sum": 1 }
      }},
      { "$replaceRoot": {
        "newRoot": {
          "$mergeObjects": ["$$ROOT", "$_id"]
        }
      }}
    ]
  }},
  { "$project": {
    "data": {
      "$concatArrays": ["$checkouts", "$waitlist"]
    }
  }},
  { "$unwind": "$data" },
  { "$replaceRoot": { "newRoot": "$data" }},
  { "$group": {
    "_id": { "_id": "$_id", "date": "$date" },
    "waitlist": { "$sum": "$waitlist" },
    "checkouts": { "$sum": "$checkouts" }
  }}
])