Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-30918

$sum and $avg Accumulators return null when only one document in $group

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 3.4.7, 3.5.11, 3.5.12
    • Component/s: Aggregation Framework
    • None
    • ALL
    • Hide

      Using the standard dataset https://raw.githubusercontent.com/mongodb/docs-assets/primer-dataset/primer-dataset.json

      This works as expected:

      db.restaurants.aggregate([
  { '$group': {
    '_id': '$cusine',
    'avg': {
      '$avg': {
        '$avg': {
          '$map': {
            'input': {
              '$filter': {
                'input': '$grades', 
                'as': 'g',
                'cond': { '$eq': [ '$$g.grade',  'A' ] }
              }
            },
            'as': 'g',
            'in': '$$g.score'
          }
        }
      }
    }
  }},
  { "$sort": { "avg": 1 } }
])

      But this returns null values

      db.restaurants.aggregate([
  { '$group': {
    '_id': '$_id',
    'avg': {
      '$avg': {
        '$avg': {
          '$map': {
            'input': {
              '$filter': {
                'input': '$grades', 
                'as': 'g',
                'cond': { '$eq': [ '$$g.grade',  'A' ] }
              }
            },
            'as': 'g',
            'in': '$$g.score'
          }
        }
      }
    }
  }},
  { "$sort": { "avg": 1 } }
])

      And even in the simplified case

      db.restaurants.aggregate([
  { '$group': {
    '_id': '$_id',
    'avg': {
      '$avg': {
        '$avg': "$grades.score"
      }
    }
  }},
  { "$sort": { "avg": 1 } }
])
      Show
      Using the standard dataset https://raw.githubusercontent.com/mongodb/docs-assets/primer-dataset/primer-dataset.json This works as expected: db.restaurants.aggregate([ { '$group' : { '_id' : '$cusine' , 'avg' : { '$avg' : { '$avg' : { '$map' : { 'input' : { '$filter' : { 'input' : '$grades' , 'as' : 'g' , 'cond' : { '$eq' : [ '$$g.grade' , 'A' ] } } }, 'as' : 'g' , 'in' : '$$g.score' } } } } }}, { "$sort" : { "avg" : 1 } } ]) But this returns null values db.restaurants.aggregate([ { '$group' : { '_id' : '$_id' , 'avg' : { '$avg' : { '$avg' : { '$map' : { 'input' : { '$filter' : { 'input' : '$grades' , 'as' : 'g' , 'cond' : { '$eq' : [ '$$g.grade' , 'A' ] } } }, 'as' : 'g' , 'in' : '$$g.score' } } } } }}, { "$sort" : { "avg" : 1 } } ]) And even in the simplified case db.restaurants.aggregate([ { '$group' : { '_id' : '$_id' , 'avg' : { '$avg' : { '$avg' : "$grades.score" } } }}, { "$sort" : { "avg" : 1 } } ])
    • None
    • 3
    • None
    • None
    • None
    • None
    • None
    • None

      Both $sum and $avg are meant to act both as accumulators with $group as well as working with a provided array. A common usage would be to $filter array content and feed that to $avg to obtain the average for the array in the document and then "accumulate" those values with another $avg in a $group pipeline.

      When used in the style of:

      result: {
  $avg: {
    $avg: '$values'
  }
}

      This works as expected when the _id provided to $group accumulates more than one document. However if the provided property does not accumulate over more than one document, the value is returned as null.

      This affects both $avg and $sum when applied as both accumulator and array reducer.

            Assignee:
            kelsey.schubert@mongodb.com Kelsey Schubert
            Reporter:
            neillunn Neil Lunn
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated:
              Resolved: