How to group documents by field

user_10907640 0
Viewed 37

suppose I have a document in my mongo collection like this one.

{
actorId:"101"
 movieType:"action"
movieName:"movie1"
},
{
actorId:"102"
 movieType:"comedy",
movieName:"movie2"
},
{
actorId:"101"
 movieType:"action",
movieName:"movie3"
},
{
actorId:"101"
 movieType:"comedy"
name:"movie4",
},
{
actorId:"102"
 movieType:"action"
movieName:"movie5"
}

I want to know how any different types of movies did the agent have lik

agents:[
{
  id:101,
  Movietypes:[
{
 type:"action",
  count:2
},
{
 type:"comedy",
count:1
}
]
}
]

It will be very helpful if anyone can guide me on how can I get a response like the above. I searched but didn't able to find any solution, looking for your help

aggregation-framework mongodb-query mongodb
1 Answers

You can first use $group on actorId and movieType with $sum to get count of each movie type for each actor. Then another $group stage to accumulate all movie types for each actor into 1 document.

[
  {
    '$group': {
      '_id': {
        'actorId': '$actorId', 
        'movieType': '$movieType'
      }, 
      'count': {
        '$sum': 1
      }
    }
  }, {
    '$group': {
      '_id': '$_id.actorId', 
      'movieTypes': {
        '$push': {
          'type': '$_id.movieType', 
          'count': '$count'
        }
      }
    }
  }
]

This will return multiple documents (1 for each actor) and contain movieTypes array as in the screenshot below.

enter image description here

If you want to return a single document with agents array then you can add another stage as follows:

{
    '$group': {
      '_id': null, 
      'agents': {
        '$push': {
          'id': '$_id', 
          'movieTypes': '$movieTypes'
        }
      }
    }
}

The output after adding third group stage would be:

enter image description here

user_131306970
Related