Find an average day count in the array in mongo

user_7827654 0
Viewed 55

Suppose we have an array in the aggregation pipeline:

{
  dates: [
    "2019-01-29",
    "2019-01-29",
    "2019-01-29",
    "2019-01-29",
    "2019-02-06",
    "2019-02-06",
    "2019-02-06",
    "2019-02-08",
    "2019-06-04",
    "2019-06-25",
    "2019-07-26",
    "2019-08-15",
    "2019-08-15",
  ]
}

How to find an average count of the days in such an array?

The next stage of the pipeline is supposed to look like this:

dates : {
    "2019-01-29": 4,
    "2019-02-06": 3,
    "2019-02-08": 1,
    "2019-06-04": 1,
    "2019-06-25": 1,
    "2019-07-26": 1,
    "2019-08-15": 2
}

But the final result is supposed to look like this:

avg_day_count: 1.85714285714

I.e. the average count of the days.
The sum of all days divided by the count of unique days.

aggregation-framework mongodb count arrays
2 Answers

You can achieve this without any $group logic with a single $project;

db.collection.aggregate([
  {
    "$project": {
      "result": {
        $divide: [
          { $size: "$dates" },
          { $size: { $setUnion: [ "$dates" ] } }
        ]
      }
    }
  }
])

will give out;

[
  {
    "_id": ...,
    "result": 1.8571428571428572
  }
]

check the code interactively on MongoPlayground

user_36410670

You need to run $group twice using $avg in the second one:

db.collection.aggregate([
    {
        $uwnind: "$dates"
    },
    {
        $group: {
            _id: "$dates",
            count: { $sum: 1 }
        }
    },
    {
        $group: {
            _id: null,
            avg_day_count: { $avg: "$count" }
        }
    }
])

Mongo Playground

user_62389770
Related