Uploaded image for project: 'Documentation'
  1. Documentation
  2. DOCS-8996

$avg aggregation operator should return null instead of 0

    XMLWordPrintableJSON

Details

    • Icon: Task Task
    • Resolution: Done
    • Icon: Major - P3 Major - P3
    • Server_Docs_20231030
    • None
    • Server
    • None

    Description

      Currently, if you run $avg against a (nonexistent) field, the return will be 0, instead of null. If you do $min or $max, you will get a null. The reason $avg and $sum should behave this way is so that when you do two pipeline $group operators in a row, currently $avg does not behave the same in both pipelines because the first $group stage will return a 0 instead of null for records that don't have the $group _id.

      For example, imagine the records

      {'a' : 1, 'b' : 1, 'value' : 1}
      {'a' : 1, 'b' : 1, 'value' : 9}
      {'a' : 1, 'b' : 2}
      

      then if I run

      db.test.aggregate({$group : {'_id' : {'a' : '$a', 'b' : '$b'}, 'average' : {'$avg' : '$value'}}}, {$group : {'_id' : {'a' : '$_id.a'}, 'average' : {'$avg' : '$average'}}});
      

      I get 2.5 instead of 5, of course because the first $avg operation returns 0. If, however, this operation returned NULL, then it would work fine. Also, if this type of behavior is DESIRED, a simple ifnull() could always be used. However, I am of the opinion that $avg of a bunch of nulls is NULL, not 0.

      I suppose the $sum operation returning 0 instead of null is of less consequence, but it should probably behave the same way.

      Thoughts?

      Attachments

        Activity

          People

            allison.moore@mongodb.com Allison Reinheimer Moore
            emily.hall Emily Hall
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:
              7 years, 20 weeks ago