[{"_id":{"$oid":"6187e5fa4ebcc2db6c86081f"},"publicId":"S23DCL","flights":[{"name":"f1","_id":{"$oid":"620026293fc350024da614dd"},"wines":[{"id":"1","name":"wine1"},{"id":"2","name":"wine2"}]},{"name":"f2","_id":{"$oid":"6200263c3fc350024da614de"},"wines":[{"id":"3","name":"leVin"},{"id":"4","name":"theWine"}]}],"title":"Pinot Noir 2022","scores":[{"userId":"f8Dv","userName":"gugus","scores":[{"score":95,"wineId":"1"},{"score":88,"wineId":"2"}]},{"userId":"yLjh","userName":"test","scores":[{"score":92,"wineId":"1"},{"score":87,"wineId":"2"}]},{"userId":"B6em","userName":"jklsdf","scores":[{"score":88,"wineId":"1"},{"score":90,"wineId":"2"},{"score":92,"wineId":"3"},{"score":86,"wineId":"4"}],"isFinished":false}],}]

db.collection.aggregate([{"$match":{"publicId":"S23DCL"}},{"$project":{"scores":"$scores","publicId":"$publicId"}},{"$unwind":"$scores"},{"$unwind":"$scores.scores"},{$lookup:{from:"collection",let:{wId:"$scores.scores.wineId","tastingId":"$_id"},pipeline:[{"$unwind":"$flights"},{"$unwind":{"path":"$flights.wines","includeArrayIndex":"index"}},{$match:{$expr:{"$and":[{"$eq":["$flights.wines.id","$$wId"]},{"$eq":["$_id","$$tastingId"]}]},}},{$project:{_id:0,"name":"$flights.wines.name","flight":"$flights.name","wineId":"$flights.wines.id","index":"$index"}}],as:"wine"}},{"$replaceRoot":{"newRoot":{"$mergeObjects":[{"_id":"$_id","scores":"$scores"},{"wine":{"$arrayElemAt":["$wine",0]}}]}}},{"$group":{"_id":"$wine","avg":{"$avg":"$scores.scores.score"},"min":{"$min":"$scores.scores.score"},"max":{"$max":"$scores.scores.score"},"stddev":{"$stdDevPop":"$scores.scores.score"},"id":{"$max":1},"scores":{"$addToSet":"$scores"}}}])