I'm trying to sum up the number of years a player played which is set up as an array that is a property of an object which is in another array. I'm trying to do this in MongoDB but not sure what I need to do, I feel like I may be over complicating this. Here is my document structure. I'm trying to use aggregates to accomplish this.

{
  "_id": "/players/h/hunteto01.shtml",
  "url": "/players/h/hunteto01.shtml",
  "name": "Torii Hunter",
  "image": "https://www.baseball-reference.com/req/202108020/images/headshots/7/79f9873b_br.jpg",
  "teams": [
    {
      "name": "MIN",
      "years": [
        1997,1998,1999,2000,
        2001,2002,2003,2004,
        2005,2006,2007,2015
      ]
    },
    {
      "name": "LAA",
      "years": [
        2008,2009,2010,
        2011,2012
      ]
    },
    {
      "name": "DET",
      "years": [
        2013,2014
      ]
    }
  ],
  "searchName": "torii hunter"
}

In this example I would I want to see output of something like careerLength: 19

CodePudding user response：

1. $set - Set the careerLength field.

   1.1. $reduce - Iterate elements in the teams array and transform the result to a numeric value.

   1.1.1. $sum - Sum the accumulated value ($$value) with the size of the years array for the current iterated element via $size.

db.collection.aggregate([
  {
    $set: {
      careerLength: {
        $reduce: {
          input: "$teams",
          initialValue: 0,
          in: {
            $sum: [
              "$$value",
              {
                $size: "$$this.years"
              }
            ]
          }
        }
      }
    }
  }
])

Demo @ Mongo Playground