0
167
ViewsHow to group documents by field
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
1 answers
Answer question0
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.
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:
