MongoDB aggregation query to return total number of entries in a collection per year-month, grouped by a location value

Question

I'm attempting to write a mongo aggregation query that returns the total number of entries in a collection per year-month, grouped by a location.

Here is an example of the desired output:

  [
          {
            _id: 'Location 1',
            reviews: [
              { total: 12, date: '03-2019' },
              { total: 55, date: '04-2019' },
              { total: 9, date: '05-2019' }
            ]
          },
          {
            _id: 'Location 2',
            reviews: [
              { total: 8, date: '03-2019' },
              { total: 35, date: '04-2019' },
              { total: 12, date: '05-2019' }
            ]
          }
        ];

This is a truncated example of the schema:

  {
    branchId: { type: String, required: true },
    orgId: { type: String, required: true },
    stars: { type: Number, default: 0 },
    reviewUpdatedAt: { type: Date, default: Date.now }
  }

I'm able to return the data in various ways, but having issues getting the desired output. Here are some example queries where I've ran into road blocks. Basically I have issues trying to group the total entries per month, then group that further down by branchId / location.

Here is an example of grouping by the year-month. The response contains the counts per branchId, but the _ids have duplicates for the month-year for each branch. I need the _id to be the location and to have an array containing the total and the year-month as shown in the above example of the desired output.

[
      {
        $match: {
            stars: {$exists: true, $gte: 1},
            orgId: '100003'
       //   reviewUpdatedAt: { $gte: new Date(fromDate), $lte: new Date(toDate) }
        }
      },

      {
        $group: {
          _id: { $dateToString: { format: '%Y-%m', date: '$reviewUpdatedAt' }},
          branchId: {$addToSet: '$branchId'},
          total: { $sum: 1 }
        }
      },
        {
          $unwind: '$branchId'
        },
      { $sort: { _id: 1 } }
    ]

In the above example I've had some success adjusting the group and $unwind statement like so:

{
    $group: {
        _id: '$branchId',
        date: { $addToSet: { $dateToString: { format: '%Y-%m', date: '$reviewUpdatedAt' } } },
        total: { $sum: 1 }
    }
},
{ $unwind: '$date' }

However, the total $sum is not accurate and repeats for each branchId entry. How can I modify the above queries to produce the desired output? Any help would be greatly appreciated.

whoami - fakeFaceTrueSoul whoami - fakeFaceTrueSoul · Accepted Answer · 2020-03-06T21:18:25

You can try below query :

db.collection.aggregate([
  {
    $group: {
      _id: {
        date: {
          $dateToString: {
            format: "%m-%Y",
            date: "$reviewUpdatedAt"
          }
        },
        loc: "$branchId"
      },
      Total: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: "$_id.loc",
      reviews: {
        $push: {
          Total: "$Total",
          "date": "$_id.date"
        }
      }
    }
  }
])

Test : MongoDB-Playground

MongoDB aggregation query to return total number of entries in a collection per year-month, grouped by a location value

1 Answers