Say I have the following documents that represent a log of each character's dice throws in an RPG:
{ character: 'Bill', outcome: 'success', ... },
{ character: 'Jim', outcome: 'fail', ... },
{ character: 'Veronica', outcome: 'critical', ... },
...
Using an aggregation pipeline, I want to generate lifetime statistics of each character's roll outcomes:
[{
'character': 'Bill',
'outcomes': [
'critical': 8,
'success': 32,
'failure': 20,
...
]
},
{
'character': 'Veronica',
...
}
]
The $sortByCount stage would seem to be ideal; however, I can't find a way to group its results by any particular criteria; it matches against all documents. I know how I could manually match the outcome types (there are six total) and count them per character, but I'm wondering if there's a succinct way of doing so like $sortByCount. Something like:
db.characters.aggregate([
{
$lookup: {
from: 'roll_stats',
localField: '_id',
foreignField: 'charid',
as: 'roll'
}
},
{
$unwind: '$roll'
},
{
$group: {
_id: { _id: '$_id', name: '$name' },
outcomes: { $sortByCount: '$roll.outcome' }
}
},
{
$project: {
_id: 0,
name: '$_id.name',
outcomes: 1
}
}
])
Only, $sortByCount can't be used in the $group aggregation stage.
What's the best way to accomplish this?
