I have the following structure and I want to aggregate over a list of movies to get all the actors and their image.
{
"_id" : 1,
"Title" : "Pirates of the Caribbean: The Curse of the Black Pearl",
"Actors" : [
{
"Name" : "Johnny Depp",
"NameInMovie" : "Captain Jack Sparrow",
"ImageUrl" : "Johnny-Depp.png"
},
{
"Name" : "Geoffrey Rush",
"NameInMovie" : "Captain Hector Barbossa",
"ImageUrl" : "Geoffrey-Rush.png"
},
{
"Name" : "Orlando Bloom",
"NameInMovie" : "Will Turner",
"ImageUrl" : "Orlando-Bloom.png"
}
]
},
{
"_id" : 2,
"Title" : "Pirates of the Caribbean: At World's End",
"Actors" : [
{
"Name" : "Johnny Depp",
"NameInMovie" : "Captain Jack Sparrow",
"ImageUrl" : "Johnny-Depp.png"
},
{
"Name" : "Geoffrey Rush",
"NameInMovie" : "Captain Hector Barbossa",
"ImageUrl" : "Geoffrey-Rush.png"
},
{
"Name" : "Orlando Bloom",
"NameInMovie" : "Will Turner",
"ImageUrl" : "Orlando-Bloom.png"
}
]
}
Tried to get the result using the Linq AsQueryable function, but it is not supported:
var actors = Movies
.AsQueryable()
.SelectMany(x => x.Actors)
.GroupBy(x => x.Name)
.Select(a => a.First())
.ToList();
How to get this working with MongoDB driver2
The result should like like
{"Name" : "Johnny Depp",
"ImageUrl" : "Johnny-Depp.png" },
{"Name" : "Geoffrey Rush",
"ImageUrl" : "Geoffrey-Rush.png" },
{"Name" : "Orlando Bloom",
"ImageUrl" : "Orlando-Bloom.png" }
Solved with
var actors = Movies.AsQueryable()
.SelectMany(x => x.Actors)
.GroupBy(x => new Actor { Name = x.Name, ImageUrl = x.ImageUrl })
.Select(x => x.Key)
.ToList();
This gives the following query
[{
"$unwind": "$Actors"
},
{
"$project": {
"Actors": "$Actors",
"_id": 0
}
},
{
"$group": {
"_id": {
"ImageUrl": "$Actors.ImageUrl",
"Name": "$Actors.Name"
}
}
},
{
"$project": {
"_id": "$_id"
}
}]