7

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

1 Answer 1

10

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"}]}})
Sign up to request clarification or add additional context in comments.

3 Comments

Aaaah yes.. As soon as I posted the question, i've seen $expr in the documentation ;-).. Should haved looked more closely.. Thanks
Is there any wat we can apply 2 conditions in $expr? I mean if date match vand and the status?
@KishorPatidar See the $and operator

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.