Get Total Count using aggregate + facet using Mongo

Question

I want to group my data based on event date with pagination. However what i am getting is whole record totalcount instead of eventDate count. because of this UI part is not working properly. Here is my collection sample:

{
   "_id" : ObjectId("5fc4d0009a25e8cfbe306381"),
   "eventDate" : ISODate("2021-11-29T01:00:00.000Z"),
   "team1" : {
      "tName" : "Chicago Bears",
    },
   "team2" : {
       "tName" : "Green Bay Packers",
    }
}
{
   "_id" : ObjectId("5fc4d0019a25e8cfbe3063ff"),
   "eventDate" : ISODate("2021-11-30T01:00:00.000Z"),
   "team1" : {
       "tName" : "Nashville SC",
    },
    "team2" : {
        "tName" : "Columbus Crew",
    }
}
{
   "_id" : ObjectId("5fc4d0019a25e8cfbe3063f4"),
   "eventDate" : ISODate("2021-11-30T01:00:00.000Z"),
   "team1" : {
       "tName" : "yyyy",
    },
    "team2" : {
        "tName" : "xxxx",
    }
}

here is my query:

db.getCollection('game').aggregate([
   { $addFields: { "newEventDate":  {$dateToString:{ format: "%Y-%m-%d", date: "$eventDate" }}}},
{ "$match": { 
    "eventDate": { $gte: new Date() }
    } 
},
{ "$facet": {
    "resultData": [
        { "$match": { 
            "eventDate": { $gte: new Date() }
            } 
        },
        { "$group": {
                    "_id": "$newEventDate",
                    "games": {$push: {
                                team1:"$team1",
                                team2:"$team2"
                    }}
            }
        }, 
 { $sort: {eventDate: 1} },
       {
    $limit: 1
    }
  ],    
      "pageInfo": [
       { "$count": "totalRecords" }
    ]}
  }
]);

After executing this query this is my response:

 {
"resultData" : [ 
    {
        "_id" : "2021-11-29",
        "games" : [ 
            {
                "awayTeam" : {
                    "tName" : "Chicago Bears"
                },
                "homeTeam" : {
                    "tName" : "Green Bay Packers"
                }
            }, 
        ]
    }
],
"pageInfo" : [ 
    {
        "totalRecords" : 3  **[here i want 2 ie total event date]**
    }
]
 }

user_8987128 · Answer

$match your condition
move your $group stage outside from $facet, convert your date from string inside group, add you date in group stage because we are going to sort in next stage
$sort by eventDate ascending order
$facet, first get single record using $limit, and second part get total count of the record using $count

db.collection.aggregate([
  { $match: { eventDate: { $gte: new Date() } } },
  {
    $group: {
      _id: {
        $dateToString: {
          format: "%Y-%m-%d",
          date: "$eventDate"
        }
      },
      eventDate: { $first: "$eventDate" },
      games: {
        $push: {
          team1: "$team1",
          team2: "$team2"
        }
      }
    }
  },
  { $sort: { eventDate: 1 } },
  {
    $facet: {
      resultData: [{ $limit: 1 }],
      pageInfo: [{ $count: "totalRecords" }]
    }
  }
])

Playground

Get Total Count using aggregate + facet using Mongo

1 Answers