Project with Match in aggregate not working in mongodb

Question

I am trying to fetch data based on some match condition. First I've tried this: Here ending_date is full date format

    Offer.aggregate([ 
    { 
        $match: { 
            carer_id : req.params.carer_id,
            status : 3
        }
    },
    {
        $group : {
            _id : { year: { $year : "$ending_date" }, month: { $month : "$ending_date" }}, 
            count : { $sum : 1 }
        }
    }], 
    function (err, res)
    { if (err) ; // TODO handle error 
         console.log(res); 
    });

which gives me following output:

[ { _id: { year: 2015, month: 11 }, count: 2 } ]

Now I want to check year also, so I am trying this:

    Offer.aggregate([
    {
        $project: {
            myyear: {$year: "$ending_date"}
        }
    },
    { 
        $match: { 
            carer_id : req.params.carer_id,
            status : 3,
            $myyear : "2015"
        }
    },
    {
        $group : {
            _id : { year: { $year : "$ending_date" }, month: { $month : "$ending_date" }}, 
            count : { $sum : 1 }
        }
    }], 
    function (err, res)
    { if (err) ; // TODO handle error 
         console.log(res); 
    });

which gives me following output:

 []

as you can see, _id has 2015 as a year, so when I match year it should be come in array. But I am getting null array. Why this?

Is there any other way to match only year form whole datetime?

Here is the sample data

{
    "_id": {
        "$oid": "56348e7938b1ab3c382d3363"
    },
    "carer_id": "55e6f647f081105c299bb45d",
    "user_id": "55f000a2878075c416ff9879",
    "starting_date": {
        "$date": "2015-10-15T05:41:00.000Z"
    },
    "ending_date": {
        "$date": "2015-11-19T10:03:00.000Z"
    },
    "amount": "850",
    "total_days": "25",
    "status": 3,
    "is_confirm": false,
    "__v": 0
}
{
    "_id": {
        "$oid": "563b5747d6e0a50300a1059a"
    },
    "carer_id": "55e6f647f081105c299bb45d",
    "user_id": "55f000a2878075c416ff9879",
    "starting_date": {
        "$date": "2015-11-06T04:40:00.000Z"
    },
    "ending_date": {
        "$date": "2015-11-16T04:40:00.000Z"
    },
    "amount": "25",
    "total_days": "10",
    "status": 3,
    "is_confirm": false,
    "__v": 0
}

Answer 1

You forgot to project the fields that you're using in $match and $group later on. For a quick fix, use this query instead:

Offer.aggregate([
{
    $project: {
        myyear: { $year: "$ending_date" },
        carer_id: 1,
        status: 1,
        ending_date: 1
    }
},
{ 
    $match: { 
        carer_id: req.params.carer_id,
        myyear: 2015,
        status: 3
    }
},
{
    $group: {
        _id: {
            year: { $year: "$ending_date" },
            month: { $month: "$ending_date" }
        }, 
        count: { $sum: 1 }
    }
}], 
function (err, res)
{
    if (err) {} // TODO handle error 
    console.log(res); 
});

That said, Blakes Seven explained how to make a better query in her answer. I think you should try and use her approach instead.

Answer 2

You are doing so many things wrong here, that it really warrants an explanation so hopefully you learn something.

Its a Pipeline

---

It's the most basic concept but the one people do not pick up on the most often ( and even after continued use ) that the aggregation "pipeline" is just exactly that, being "piped" processes that feed input into the each stage as it goes along. Think "unix pipe" |:

ps -ef | grep mongo | tee out.txt

You've seen something similar before no doubt, and it's the basic concept that output from the first thing goes to the next thing and then that manipulates to give input to the next thing and so on.

So heres the basic problem with what you are asking:

    {
        $project: {
            myyear: {$year: "$ending_date"}
        }
    },
    { 
        $match: { 
            carer_id : req.params.carer_id,
            status : 3,
            $myyear : "2015"
        }
    },

Consider what $project does here. You specify fields you want in the output and it "spits them out", and possibly with manipulation. Does it output these fields in "addition" the the fields in the document? No It does not. Only what you ask to come out actually comes out and can be used in the following pipeline stage(s).

The $match here essentially asks for fields that are no longer present, because you only asked for one thing in the output. The same problem occurs further down as again you ask for fields you removed earlier and there simply is nothing to reference, but also everything was already removed by a $match that could not match anything.

Also, that is not how field projections work as you have entered

So just use a range for the date

---

        { "$match": { 
            "carer_id" : req.params.carer_id,
            "status" : 3,
            "ending_date": { 
                "$gte": new Date("2015-01-01"), 
                "$lt": new Date("2016-01-01")
            }
        }},
        { "$group": {
            "_id": { 
                "year": { "$year": "$ending_date" }, 
                "month": { "$month": "$ending_date" }
            }, 
            "count": { "$sum": 1 }
        }}

Why? Because it just makes sense. If you want to match the "year" then supply the date range for the whole year. We could play silliness with $redact to match on the extracted year value, but that is just wasted processing time.

Doing this way is the fastest to process and can actually use an index to process faster. So don't otherthink the problem and just ask for the date range you want.