I need to find the average length of each Horror Series.
I have two collections Series and Books, books belongs to series and have field lenght, series got field "genre".
Series
{
"_id": "61179c8d43400f31df195223",
"name": "serie1",
"book genre": Horror
},
{
"_id": "61179c8d43400f31df195223",
"name": "serie2",
"book genre": Romance
},
{
"_id": "61179c8d43400f31df195223",
"name": "serie3",
"book genre": Horror
},
{
"_id": "61179c8d43400f31df195223",
"name": "serie4",
"book genre": Horror
},
Books
{
"_id": "61179c8d43400f31df195223",
"name": "book1",
"lenght": 100,
"serie": "serie1"
},
{
"_id": "61179c8d43400f31df195223",
"name": "book2",
"lenght": 10,
"serie": "serie3"
},
{
"_id": "61179c8d43400f31df195223",
"name": "book3",
"lenght": 900,
"serie": "serie3"
},
{
"_id": "61179c8d43400f31df195223",
"name": "book4",
"lenght": 300,
"serie": "serie1"
},
You can do this with a pretty standard aggregation, here i'm using $lookup, $avg and $map to achieve the end results:
db.series.aggregate([
{
$lookup: {
from: "books",
localField: "name",
foreignField: "serie",
as: "books"
}
},
{
$project: {
_id: 1,
name: 1,
"book genre": 1,
avg: {
"$ifNull": [
{
$avg: {
$map: {
input: "$books",
in: "$$this.lenght"
}
}
},
0
]
}
}
}
])
Query
*Toms answer found average per series, if you don't want that maybe you want this? i am not sure. It would help if you sended us the expected output, i thought you wanted what Tom did also.
series.aggregate(
[{"$group":{"_id":"$book genre", "series":{"$push":"$name"}}},
{"$lookup":
{"from":"books",
"localField":"series",
"foreignField":"serie",
"as":"series"}},
{"$set":
{"book genre":"$_id",
"_id":"$$REMOVE",
"avgTime":{"$avg":"$series.lenght"}}}])