Mongodb aggregation - Match for current month

Question

I am defining a mongodb aggregate expression which will be stored in JSON and executed. The current aggregate expression is mostly simple grouping and projection.

for e.g. below is the aggregate query for grouping in follow up date

[{
            "$group": {
                "_id": {
                    "day_of_month": {
                        "$dayOfMonth": "$data.follow_up_date"
                    },
                    "month": {
                        "$month": "$data.follow_up_date"
                    },
                    "year": {
                        "$year": "$data.follow_up_date"
                    }
                },
                "total_leads": {
                    "$sum": 1
                },
                "total_loan_amount": {
                    "$sum": "$data.loan_amount"
                },
                "follow_up_date": {
                    "$push": "$data.follow_up_date"
                },
                "data": {
                    "$push": "$data"
                }
            }
        },
        {
            "$project": {
                "_id": 1,
                "day_of_month": "$_id.day_of_month",
                "month": "$_id.month",
                "year": "$_id.year",
                "total_leads": "$total_leads",
                "total_loan_amount": "$total_loan_amount",
                "follow_up_date": {
                    "$arrayElemAt": ["$follow_up_date", 0]
                },
                "data": "$data"
            }
        }]

In this aggregate expression I would like to add dynamic date based filter which would allow me to fetch date only say for current month or current day of month.

for e.g.

[{
            "$group": {
                "_id": {
                    "day_of_month": {
                        "$dayOfMonth": "$data.follow_up_date"
                    },
                    "month": {
                        "$month": "$data.follow_up_date"
                    },
                    "year": {
                        "$year": "$data.follow_up_date"
                    }
                },
                "total_leads": {
                    "$sum": 1
                },
                "total_loan_amount": {
                    "$sum": "$data.loan_amount"
                },
                "follow_up_date": {
                    "$push": "$data.follow_up_date"
                },
                "data": {
                    "$push": "$data"
                }
            }
        },
    {
      //something like this 
      "_id.day_of_month" : {$month : new Date()}
    },
        {
            "$project": {
                "_id": 1,
                "day_of_month": "$_id.day_of_month",
                "month": "$_id.month",
                "year": "$_id.year",
                "total_leads": "$total_leads",
                "total_loan_amount": "$total_loan_amount",
                "follow_up_date": {
                    "$arrayElemAt": ["$follow_up_date", 0]
                },
                "data": "$data"
            }
        }]

Is there any way to add dynamic date filters ?

Similar question to https://stackoverflow.com/questions/18907566/mongodb-query-specific-monthyear-not-date — loonytoons, Jun 12 '19 at 23:28

loonytoons · Accepted Answer · 2019-06-13T08:40:50.760

2

There's 2 ways of doing this, though with both methods I'd recommend doing an initial $match before the $group stage to limit the amount of data being passed through the rest of the pipeline if you only need a subset of it.

Simplest way is probably working out the dates you want to query up front, so start/end date of the particular day or month you're interested in, and doing a comparison check against that:

{
    '$match': {
        '$data.follow_up_date': {
            '$lte': ...
            '$gte': ...
        }
    }
}

Otherwise you can use $expr to take the follow up date and the current date and compare some formatted values, so:

{
    '$match': {
        '$expr': {
            '$eq': [
                { "$dateToString": { format: "%Y-%m-%d", date: "$data.follow_up_date" }},
                { "$dateToString": { format: "%Y-%m-%d", date: new Date() }},
             ]
        } 
    }
}

Then you just need to change the format to change the level of specificity of the comparison.

Personally I'd recommend the first one as it's less processing and is an actual date comparison rather than a string one, but both should work

edited Jun 13 '19 at 08:40

answered Jun 12 '19 at 23:27

loonytoons

123
6

Thanks, the query works. I problem I have is since the query is stored as JSON and executed, I would need to have dynamic placeholder for the current date. However JSON doesn't recognise new Date or ISODate(). I think I would still need to modify the query on application to add my expression and then execute on mongo. Do you see any workaround this? – Sharath Chandra Jun 13 '19 at 05:34
I don't think even MongoDB Extended JSON would help you with that, and although there is a `$currentDate` in v4.0 that only works on updates at the moment. I can't see any other way than either specifying the dates to query by or switching out your own placeholder before sending the query to mongo. – loonytoons Jun 13 '19 at 09:11
agreed, I ended with adding date placeholders to my query and managing it at the application level – Sharath Chandra Jun 13 '19 at 13:58

Mongodb aggregation - Match for current month

1 Answers1