I have a collection of documents that look like this:
[
{ group_id: 1, value: 'foo' },
{ group_id: 1, value: 'bar' },
{ group_id: 1, value: 'bar' },
{ group_id: 1, value: 'bar' },
{ group_id: 2, value: 'bar' },
{ group_id: 2, value: 'foo' },
{ group_id: 2, value: 'foo' },
{ group_id: 2, value: 'foo' }
]
For each group_id I want to return the value that occurs the most. So my output should look something like this...
[
{ group_id: 1, maxValue: 'bar', maxValueCount: 3 },
{ group_id: 2, maxValue: 'foo', maxValueCount: 3 }
]
How would you do this using the Mongoose aggregate function?
Update: This is as far as I've gotten, I just need to return the value of the maximum count now...
const records = await Record.aggregate([
{
$group: {
_id: {
id: '$group_id',
value: '$value'
},
count: { $sum: 1 }
}
}
])
You may achieve your goal by
data=[
{ group_id: 1, value: 'foo' },
{ group_id: 1, value: 'bar' },
{ group_id: 1, value: 'bar' },
{ group_id: 1, value: 'bar' },
{ group_id: 1, value: 'bar' },//one more added
{ group_id: 2, value: 'bar' },
{ group_id: 2, value: 'foo' },
{ group_id: 2, value: 'foo' },
{ group_id: 2, value: 'foo' }
]
db.products.remove({})
db.products.insert(data)
const stages = [
{
$group: {
_id: {
group_id: '$group_id',
value: '$value'
},
n: { $sum: 1 }
}
},
{
$group: {
_id: '$_id.group_id',
values: {
$push: {
value: '$_id.value',
n: '$n'
}
}
}
},
{
$project: {
group_id:1,
best: {
$reduce: {
input: '$values',
initialValue: { n: 0, value: ''},
in: {
$cond: [
{
$lt: ['$$value.n', '$$this.n']
},
'$$this',
'$$value'
]
}
}
}
},
},
{
$project: {
group_id: 1,
value: '$best.value',
maxValue: '$best.n'
}
}
]
printjson(db.products.aggregate(stages).toArray())