Aggregation into separate groups

MongoDB Atlas App Services & Realm
,
1

Hello,

I have a collection with documents that look like this:

{ 
  Date        : xxx,
  userId      : yyy,
  dataType : zzz
  value       : aaa 
}

I am trying to run an aggregate query to output the following:

{
   _id           : zzz, (this will be unique)
   date         : xxx, (this is a unique list for each _id)
   total         : sum(aaa) (one number for each date)
}

Here’s my Stitch function’s aggregation code, the function receives the argument ‘userIds’:

        result = collection.aggregate([
          { $match: { userId: { "$in" : userIds } } },
          { $group: { _id  : "$dataType", 
                           date: { "$first": "$Date" }, 
                           total: { $sum: "$value" } 
          } },
          { $sort : { _id : 1 } } 
          ])

This currently outputs one date per dataType, but I’d like the results to look like this:

{ _id: dataType1, date: date1, total: 10},
{ _id: dataType1, date: date2, total: 20},
{ _id: dataType2, date: date1, total: 3},
{ _id: dataType2, date: date2, total: 77} etc...

or even like this…

{ _id: dataType1, {date: date1, total: 10}, {date: date2, total: 20}},
{ _id: dataType2, {date: date1, total: 3}, {date: date2, total: 77}} etc...

How can I get this working correctly?

As a bonus, I’d also like the results to be sorted by date within each subgroup of dataType.

2

The following aggregation does the grouping by dataType and Date fields and the summing.

db.collection.aggregate( [
  { 
    $match: { userId: { "$in" : userIds } } 
  },
  { 
    $group: { 
        _id: { 
            "dataType": "$dataType", 
            "Date": "$Date" 
        }, 
        total: { $sum: "$value" } 
    } 
  },
  { 
    $project: { 
        dataType: "$_id.dataType", 
        Date: "$_id.Date", 
        _id: 0, 
        total: 1 
    } 
  },
  { 
    $sort: { 
        dataType: 1, 
        Date: 1 
    } 
  },
  { 
    $group: { 
        _id: "$dataType", 
        DateTotals: { $push: { Date: "$Date", total: "$total" } } 
    } 
  }
] )

The output is similar to what you are expecting. Using the last $group stage (the one after the sort) is optional; depends upon how you want the output.

3

That’s fantastic, thanks so much for your help!

:grin: