Need some assistance grouping and totalling in an aggregation

Working with Data
1

Hey all,

I’m working on grouping and totalling log events (group by user and total of events of each type for each user). I’ve made some progress but I could use some pointers to help me get the results I’m looking for.

My code so far

[
        // Find all tester events in current month
        {
          $match: {
            'user.role': 'tester',
            datetime: {
              $gte: moment().startOf('month').toDate(),
              $lte: moment().endOf('month').toDate(),
            },
          },
        },
        // Group by tester + event type and project the total
        {
          $group: {
            _id: { testerId: '$user._id', eventId: '$eventId' },
            total: { $sum: 1 },
          },
        },
]

This is giving me the user Id and event type with total for each event type, but all at the “top level” and not as I intend, which would be something like this:

[
  {      
   _id: [user _id],
   user: [whole $user object],
   events: {
       'login': 10,
       'test-completed': 3,
       'password-reset': 1
   }
  }
]

I hope that this makes sense, if I’m not making sense please let me know and I’ll try to clarify.

Thanks for any help you can offer :slight_smile:

2

It would be easier if you include actual raw data, as well as syntactically correct data that isn’t dependent on third party libraries (e.g. moment). This way, it can just be easily pasted into any environment and it’s a ton easier to help. :+1:

I assume it’s something like

db.test.drop();
db.test.insertMany([
{user: {_id: '123', role: 'tester'}, datetime: '2020-01-01T00:00:00', eventId: 'login'},
{user: {_id: '123', role: 'tester'}, datetime: '2020-01-01T01:00:00', eventId: 'test-completed'},
{user: {_id: '456', role: 'tester'}, datetime: '2020-01-01T02:00:00', eventId: 'login'},
{user: {_id: '123', role: 'tester'}, datetime: '2020-01-01T03:00:00', eventId: 'password-reset'},
{user: {_id: '123', role: 'tester'}, datetime: '2020-01-01T04:00:00', eventId: 'login'}
])

And you are doing

db.test.aggregate([
  {
    $match: {
      'user.role': 'tester',
      datetime: {
        $gte: '2020-01-01T00:00:00',
        $lte: '2020-02-01T00:00:00',
      },
    },
  },
  {
    $group: {
      _id: { testerId: '$user._id', eventId: '$eventId' },
      total: { $sum: 1 },
    },
  },
]);

If you want these results to be grouped, just do another group:

db.test.aggregate([
  {
    $match: {
      'user.role': 'tester',
      datetime: {
        $gte: '2020-01-01T00:00:00',
        $lte: '2020-02-01T00:00:00',
      },
    },
  },
  {
    $group: {
      _id: { testerId: '$user._id', eventId: '$eventId' },
      total: { $sum: 1 },
    },
  },
  {
    $group: {
      _id: '$_id.testerId',
      events: {$push: {eventId: '$_id.eventId', total: {$sum: '$total'}}}
    },
  },
]);
1 Like
3

Hi,

Yes your assumptions were correct, but I’ll be clearer about the data I’m dealing with in the future :slight_smile:

I have a lot to learn to really be comfortable with getting the data out of Mongo that I want - the $push and double-grouping stuff just didn’t even occur to me to be something I would want to reach for, so thanks for the example!