Counting value of subdocuments in mongoose subquery

user_10637188 0
Viewed 269

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 }
  }
}
])
mongoose node.js mongodb
1 Answers

You may achieve your goal by

  • grouping by group_id/value (and counting the occurrences of value)
  • then grouping by group_id and pushing all the found values within with their count
  • finally keeping from the array the value having the max count
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())

playground

user_7536420
Related