Fetch data between two dates and with a specific value in MongoDB. Group and get the sum with count?

To fetch data between two dates and with a specific value in MongoDB, use $match to filter documents and $group to aggregate results. The $gte and $lte operators handle date range filtering.

Syntax

db.collection.aggregate([
    {
        $match: {
            "fieldName": "specificValue",
            "dateField": {
                "$gte": ISODate("startDate"),
                "$lte": ISODate("endDate")
            }
        }
    },
    {
        $group: {
            "_id": "$groupByField",
            total: { $sum: "$sumField" },
            count: { $sum: 1 }
        }
    }
])

Sample Data

db.demo560.insertMany([
    {"value1": 40, "value2": 40, shippingDate: new ISODate("2020-02-26")},
    {"value1": 20, "value2": 60, shippingDate: new ISODate("2020-02-26")},
    {"value1": 40, "value2": 70, shippingDate: new ISODate("2020-03-31")},
    {"value1": 40, "value2": 130, shippingDate: new ISODate("2020-03-31")}
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5e8f3d5254b4472ed3e8e867"),
        ObjectId("5e8f3d5254b4472ed3e8e868"),
        ObjectId("5e8f3d5254b4472ed3e8e869"),
        ObjectId("5e8f3d5254b4472ed3e8e86a")
    ]
}

View Sample Data

db.demo560.find();

{ "_id": ObjectId("5e8f3d5254b4472ed3e8e867"), "value1": 40, "value2": 40, "shippingDate": ISODate("2020-02-26T00:00:00Z") }
{ "_id": ObjectId("5e8f3d5254b4472ed3e8e868"), "value1": 20, "value2": 60, "shippingDate": ISODate("2020-02-26T00:00:00Z") }
{ "_id": ObjectId("5e8f3d5254b4472ed3e8e869"), "value1": 40, "value2": 70, "shippingDate": ISODate("2020-03-31T00:00:00Z") }
{ "_id": ObjectId("5e8f3d5254b4472ed3e8e86a"), "value1": 40, "value2": 130, "shippingDate": ISODate("2020-03-31T00:00:00Z") }

Example: Filter by Value and Date Range

Find documents where value1 = 40 and shippingDate is between Feb 26 and March 31, 2020, then group by date ?

db.demo560.aggregate([
    {
        $match: {
            "value1": 40,
            "shippingDate": {
                "$gte": ISODate("2020-02-26"),
                "$lte": ISODate("2020-03-31")
            }
        }
    },
    {
        $group: {
            "_id": "$shippingDate",
            total: { $sum: "$value2" },
            count: { $sum: 1 }
        }
    }
])

{ "_id": ISODate("2020-03-31T00:00:00Z"), "total": 200, "count": 2 }
{ "_id": ISODate("2020-02-26T00:00:00Z"), "total": 40, "count": 1 }

How It Works

• $match stage filters documents where value1 equals 40 and shippingDate falls within the specified range
• $group stage groups filtered documents by shippingDate
• $sum: "$value2" calculates the total of value2 for each group
• $sum: 1 counts the number of documents in each group

Conclusion

Use $match with date range operators $gte and $lte to filter documents, then apply $group to aggregate results with sum and count operations for effective data analysis.