I have a following reportfinals collection and it has the following format:

{
  username: 123,
  rxm_number: 5,
  shelf_number: 12345
}

There may be more documents as well. I wanted to group both by rxm_number and shelf_number and count username to display something like this:

[
 {
   rxm_number: 1,
   shelf_number: 1235,
   users: 150
 },
{
   rxm_number: 1,
   shelf_number: 442,
   users: 68
 },
{
   rxm_number: 2,
   shelf_number: 44,
   users: 68
 },
]

I used the aggregate function with $group and $project and my query looks like this:

db.reportfinals.aggregate([
    {$group: {
        _id: '$rxm_number', 
        shelf_number: {$addToSet: '$shelf_number'}, 
        users: {$addToSet: '$username'},

    }},
    {$project: {
        _id: 0, 
        rxm_number: '$_id', 
        shelf_number: {$size: '$shelf_number'}, 
        users: {$size: '$users'}
    }}
])

But it returns a single object formatted like this:

{
 rxm_number: 2,
 shelf_number: 25,
 users: 34
}

How can I modify this query to achieve the result I wanted above?

CodePudding user response：

Simply using both values into the _id object inside $group like this:

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        "username": "$rxm_number",
        "shelf_number": "$shelf_number"
      },
      "users": {
        "$sum": 1
      }
    }
  }
])

Example here