[MongoDB] - Filtering on empty date broken

[Serfenia]

Hello. When I'm adding a filter on a Date field in Metabase for a MongoDB database, it's not allowing me to check whether it is 'empty' (equal to null or does not exist).

The filter:

This is the native query it's using:

[{
    "$project": {
        "shipped_at~~~day": {
            "$let": {
                "vars": {
                    "field": "$shipped_at"
                },
                "in": {
                    "___date": {
                        "$dateToString": {
                            "format": "%Y-%m-%d",
                            "date": "$$field"
                        }
                    }
                }
            }
        }
    }
}, {
    "$match": {
        "shipped_at~~~day": {
            "$eq": null
        }
    }
}]

The issue here is that the query is asserting shipped_at~~~day to null. This is never going to work, since the projection results into an object with a nested field called ___date:

{ "_id" : ObjectId(<some id>), "shipped_at~~~day" : { "___date" : "2016-01-18" } }
{ "_id" : ObjectId(<some id>), "shipped_at~~~day" : { "___date" : "2016-01-22" } }
{ "_id" : ObjectId(<some id>), "shipped_at~~~day" : { "___date" : "2016-01-18" } }
{ "_id" : ObjectId(<some id>), "shipped_at~~~day" : { "___date" : null } }

So the assertion should not be:

"shipped_at~~~day": {
    "$eq": null
}

But instead:

"shipped_at~~~day.___date": {
    "$eq": null
}

I hope you can fix this soon.

• Browser: Chrome 56.0
• Metabase: 0.22.2
• Operating System: macOS
• Database: MongoDB 3.2.10

[BenHizak]

Please be aware that {key : null} and {key : {$exists : false}} have two different meanings in MongoDB, both of which are necessary. This is the source of much frustration at times.

[BenHizak]

Proposed workaround (see last item) #4789

[Serfenia]

Metabase tries to use terms so it is understandable for people besides tech people. The value null in the meaning of a user that doesn't know specifics of MongoDB is equal to does not exist, the terms that Metabase uses. So in that case the resulting query should have an $or statement checking for null or $exists: false.

Besides that @Behiz , your proposal does not fix the issue (although I like it). This is just a code issue that should be solved.

[camsaul]

This might be fixed in 0.31.0

[flamber]

Closing, since no reports that it wasn't fixed in 0.31.0