MongoDB aggregate to get the Mean daily average count of recorded documents in a collection?

To get the mean daily average count of recorded documents in MongoDB, use the aggregate() method with $match, $group, and $project stages to filter dates, calculate totals, and compute the average based on time differences.

Syntax

db.collection.aggregate([
    { $match: { dateField: { $lt: new ISODate() } } },
    { $group: { _id: null, oldestDate: { $min: "$dateField" }, totalValue: { $sum: "$valueField" } } },
    { $project: { averageValue: { $divide: ["$totalValue", { $divide: [{ $subtract: [new ISODate(), "$oldestDate"] }, 86400000] }] } } }
]);

Sample Data

Let us create a collection with documents ?

db.demo451.insertMany([
    {
        DueDate: new ISODate("2020-03-15T10:50:35.000Z"),
        Value: 10
    },
    {
        DueDate: new ISODate("2020-03-14T10:50:35.000Z"),
        Value: 10
    },
    {
        DueDate: new ISODate("2020-03-13T10:50:35.000Z"),
        Value: 10
    }
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5e7b5c5d71f552a0ebb0a6e9"),
        ObjectId("5e7b5c5d71f552a0ebb0a6ea"),
        ObjectId("5e7b5c5d71f552a0ebb0a6eb")
    ]
}

Display all documents from the collection ?

db.demo451.find();

{ "_id": ObjectId("5e7b5c5d71f552a0ebb0a6e9"), "DueDate": ISODate("2020-03-15T10:50:35Z"), "Value": 10 }
{ "_id": ObjectId("5e7b5c5d71f552a0ebb0a6ea"), "DueDate": ISODate("2020-03-14T10:50:35Z"), "Value": 10 }
{ "_id": ObjectId("5e7b5c5d71f552a0ebb0a6eb"), "DueDate": ISODate("2020-03-13T10:50:35Z"), "Value": 10 }

Example

Calculate the mean daily average count of recorded documents ?

db.demo451.aggregate([
    {
        $match: {
            DueDate: {
                $lt: new ISODate()
            }
        }
    },
    {
        $group: {
            _id: null,
            olderDate: {
                $min: "$DueDate"
            },
            sumValue: {
                $sum: "$Value"
            }
        }
    },
    {
        $project: {
            _id: 0,
            averageValue: {
                $divide: ["$sumValue", {
                    $divide: [{
                        $subtract: [new ISODate(), "$olderDate"]
                    }, 1000 * 60 * 60 * 24]
                }]
            }
        }
    }
]);

{ "averageValue": 2.475558456547562 }

How It Works

• $match: Filters documents with DueDate before current time
• $group: Finds the oldest date and sums all Value fields
• $project: Calculates daily average by dividing total value by number of days between oldest date and now
• The division by 1000 * 60 * 60 * 24 converts milliseconds to days

Conclusion

Use MongoDB's aggregation pipeline with date calculations to find mean daily averages. The key is dividing total values by the time span in days between the oldest record and current date.