Find the average value, MongoDB-CodePudding

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"
},

CodePudding user response：

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
        ]
      }
    }
  }
])

Mongo Playground

CodePudding user response：

Query

finds the average per genre, for example average length of all horror movies
group per genre
lookup to get the lengths
find the average

*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.

Test code here

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"}}}])