The average array of the Mongodb group

Question

I am trying to aggregate PyMongo - the average values ​​in a group of arrays, and I can not find examples that match my problem.

Sample data

{
    Subject: "Dave",
    Strength: [1,2,3,4]
},
{
    Subject: "Dave",
    Strength: [1,2,3,5]
},
{
    Subject: "Dave",
    Strength: [1,2,3,6]
},
{
    Subject: "Stuart",
    Strength: [4,5,6,7]
},
{
    Subject: "Stuart",
    Strength: [6,5,6,7]
},
{
    Subject: "Kevin",
    Strength: [1,2,3,4]
},
{
    Subject: "Kevin",
    Strength: [9,4,3,4]
}

Required Results

{
    Subject: "Dave",
    mean_strength = [1,2,3,5]
},
{
    Subject: "Stuart",
    mean_strength = [5,5,6,7]
},
{
    Subject: "Kevin",
    mean_strength = [5,3,3,4]
}

I tried this approach, but does MongoDB interpret arrays as Null?

pipe = [{'$group': {'_id': 'Subject', 'mean_strength': {'$avg': '$Strength'}}}]
results = db.Walk.aggregate(pipeline=pipe)

Out: [{'_id': 'SubjectID', 'total': None}]

I looked through the MongoDB documentation and I cannot find or understand if there is a way to do this?

Answer 1

$unwind includeArrayIndex. , includeArrayIndex . Subject Strength. $group $push . , $project .

db.test.aggregate([{
        "$unwind": {
            "path": "$Strength",
            "includeArrayIndex": "rownum"
        }
    },
    {
        "$group": {
            "_id": {
                "Subject": "$Subject",
                "rownum": "$rownum"
            },
            "mean_strength": {
                "$avg": "$Strength"
            }
        }
    },
    {
        "$sort": {
            "_id.Subject": 1,
            "_id.rownum": 1
        }
    },
    {
        "$group": {
            "_id": "$_id.Subject",
            "mean_strength": {
                "$push": "$mean_strength"
            }
        }
    },
    {
        "$project": {
            "_id": 0,
            "Subject": "$_id",
            "mean_strength": 1
        }
    }
])

:

{ "mean_strength" : [ 5, 5, 6, 7 ], "Subject" : "Stuart" }
{ "mean_strength" : [ 5, 3, 3, 4 ], "Subject" : "Kevin" }
{ "mean_strength" : [ 1, 2, 3, 5 ], "Subject" : "Dave" }

Answer 2

.

, Dave [[1,2,3,4], [1,2,3,5], [1,2,3,6]] .

Pass   Current Value (c) Accumulated Value (b)       Next Value
First:   [1,2,3,5]        [[1],[2],[3],[4]]           [[1,1],[2,2],[3,3],[5, 4]]
Second:  [1,2,3,6]        [[1,1],[2,2],[3,3],[5, 4]]  [[1,1,1],[2,2,2],[3,3,3],[5, 4, 6]]

. avg [1,2,3,5]

[{"$group":{"_id":"$Subject","Strength":{"$push":"$Strength"}}}, //Push all arrays
 {"$project":{"mean_strength":{
   "$map":{//Calculate avg for each reduced indexed pairs.
     "input":{
       "$reduce":{
         "input":{"$slice":["$Strength",1,{"$subtract":[{"$size":"$Strength"},1]}]}, //Start from second array.
         "initialValue":{ //Initialize to the first array with all elements transformed to array of single values.
           "$map":{
             "input":{"$range":[0,{"$size":{"$arrayElemAt":["$Strength",0]}}]},
             "as":"a",
             "in":[{"$arrayElemAt":[{"$arrayElemAt":["$Strength",0]},"$$a"]}]
           }
         },
         "in":{
           "$let":{"vars":{"c":"$$this","b":"$$value"}, //Create variables for current and accumulated values
             "in":{"$map":{ //Creates map of same indexed values from each iteration 
                 "input":{"$range":[0,{"$size":"$$b"}]},
                 "as":"d",
                 "in":{
                   "$concatArrays":[ //Concat values at same index 
                     {"$arrayElemAt":["$$c","$$d"]}, //current
                     [{"$arrayElemAt":["$$b","$$d"]}] //accumulated
                  ]
                 }
               }
             }
           }
         }
       }
     },
    "as":"e",
    "in":{"$avg":"$$e"}
   }
 }}}
]

Answer 3

As described above, as a solution for it, try the following aggregate query

db.collection.aggregate(

  // Pipeline
  [
    // Stage 1
    {
      $unwind: { path: "$Strength", includeArrayIndex: "arrayIndex" }   
    },

    // Stage 2
    {
      $group: {
        _id:{Subject:'$Subject',arrayIndex:'$arrayIndex'},
        mean_strength:{$avg:'$Strength'}
      }
    },

    // Stage 3
    {
      $group: {
      _id:{'Subject':'$_id.Subject'},
      mean_strength:{$push:'$mean_strength'}
      }
    },

    // Stage 4
    {
      $project: {
      Subject:'$_id.Subject',
      mean_strength:'$mean_strength',
      _id:0
      }
    }

  ]


);