MongoDB Match $and doesn't work but $or does

Viewed 396

I'm trying to match in a pipeline all documents between a given date range.

When using a $or I successfully get data that confirms the $or condition. However, switching it to $and I get no data back.

Match block:

{
    "fields.issueType.name" : "Bug",
        "fields.resolutionDate" : {
        "$exists" : false
    },
    $expr: {
        $and: [
            { $gte: ["fields.created", new Date("2020-01-01T00:00:00.0Z")] },
            { $lte: ["fields.created", new Date("2021-01-01T00:00:00.0Z")] }
        ]
    }
} 

A sample of the document

"fields": {
   "created" : "2020-08-03T10:50:08.626+0100", 
}

I'm reading this in m head as get all documents where fields.created is greater than 2020-01-01 and less than 2021-01-01 so I would expect all documents created in 2020.

I'm using Mongo version 4.2.8

Have I got my reasoning wrong or is there a different way I should be doing this?

Edit:

The updated query now looks like:

db.getCollection("issues").aggregate(
    [
        { 
            "$addFields" : { 
                "created_at_date" : { 
                    "$toDate" : "$fields.created"
                }
            }
        }, 
        { 
            "$match" : { 
                "fields.issueType.name" : "Bug", 
                "fields.resolutionDate" : { 
                    "$exists" : false
                }, 
                "$expr" : { 
                    "$and" : [
                        { 
                            "$gt" : [
                                "created_at_date", 
                                ISODate("2020-01-01T00:00:00.000+0000")
                            ]
                        }, 
                        { 
                            "$lt" : [
                                "created_at_date", 
                                ISODate("2021-01-01T00:00:00.000+0000")
                            ]
                        }
                    ]
                }
            }
        }, 
    ]
1 Answers

Actually your $and is working. The real issue is that it can't find any documents which would be both greater than or equal to new Date("2020-01-01T00:00:00.0Z") and less than or equal to new Date("2020-01-01T00:00:00.0Z") That's why It would always return an empty list of documents.

Use $gt and $lt instead of $gte and $lte, respectively

Related