CODEWITHSUNDEEP
mongodbaggregation-framework
narcoticfresh
narcoticfresh

Reputation: 282

MongoDB Aggregation Pipeline: $match with expression not possible?

I'm doing a rather complicated aggregation pipeline and have a rather strange phenomenon - I extracted a short example to visualize my problem here.

It seemed related to MongoDb $addFields and $match - but it doesn't contain any information for me to fix the problem at hand.

Note: Please note that my problem is not with the specific example of using date fields and or dealing with values, the problem is that I'm not able to $match using an expression - using a field that was added before with $addFields or not.

Given MongoDB: 3.6.3 (currently latest)

Let's insert some testdata:

db.testexample.insert({
   "dateField": new ISODate("2016-05-18T16:00:00Z")
});

db.testexample.insert({
   "dateField": new ISODate("2018-05-18T16:00:00Z")
});

Now let's make simple pipeline that computes only the year of the date and $matches on that:

db.testexample.aggregate([
    {
        "$addFields": {
            "dateFieldYear": {"$year": "$dateField"}
        }
    },
    {
        "$match": {
            "dateFieldYear": {"$eq": "$dateFieldYear"}}
        }
    }
])

--> No matches

It should match as it's the same field? Maybe with more trickery (using an $add)?

db.testexample.aggregate([
    {
        "$addFields": {
            "dateFieldYear": {"$year": "$dateField"}
        }
    },
    {
        "$match": {
            "dateFieldYear": {"$eq": {"$add": ["$dateFieldYear", 0]}}
        }
    }
])

--> No matches

Still no dice.. Next i thought that variables altogether are a problem. So let's fix the values:

db.testexample.aggregate([
    {
        "$addFields": {
            "dateFieldYear": {"$year": "$dateField"}
        }
    },
    {
        "$match": {
            "dateFieldYear": {"$eq": {"$add": [2016, 0]}}
        }
    }
])

--> No matches

Wait.. something is really wrong here.. Let's see with a static value:

db.testexample.aggregate([
    {
        "$addFields": {
            "dateFieldYear": {"$year": "$dateField"}
        }
    },
    {
        "$match": {
            "dateFieldYear": 2016
        }
    }
])

--> 1 record found!

So my conclusion seems to be that $match cannot take an expression on a field in an aggregate pipeline. But this doesn't seem possible - as the documentation states that $match follows the query syntax as described here.

Anybody can help how it can be done to $match using the simple example "dateFieldYear": {"$eq": "$dateFieldYear"}} - why doesn't this work as expected?

Thanks so much for any help

Upvotes: 7

Views: 10664

Answers (1)

s7vr
s7vr

Reputation: 75984

You can use $expr ( 3.6 mongo version operator ) to use aggregation functions in regular query.

Compare query operators vs aggregation comparison operators.

In your case 

db.testexample.find({$expr:{$eq:["$dateFieldYear", "$dateFieldYear"]}})

Regular Query:

db.testexample.find({$expr:{$eq:["$dateFieldYear", {"$year": "$dateField"}]}})

Aggregation Query:

db.testexample.aggregate({$match:{$expr:{$eq:["$dateFieldYear", {"$year": "$dateField"}]}})

Upvotes: 10

Related Questions