excluding from $match if data is blank or null in mongodb

Question

I am getting trouble in $match in mongodb.

Lets say I have multiple $match options in some case some options are blank or null at that stage I want to $match do match that field in our document.

For Example

$match:{
   name:'abc',
   age:''  //exclude if blank or null,
   city:'delhi'
}

I want it only match name and city field. I want it exclude auto if blank or null possible from aggregation.

Answer 1

As your question heading excluding from $match if data is blank or null in mongodb suggests that you want to exclude if data is blank and null. You should use $and to match condition like following:

db.collection.aggregate({$match:{"name":"abc","city":"delhi","$and":[{"age":{"$ne":""}},{"age":{"$ne":null}}]}} )

Answer 2

I suppose you documents look like this:

{ "_id" : ObjectId("55c87313fc92af6b6b2f9497"), "name" : "abc", "age" : "", "city" : "delhi" }
{ "_id" : ObjectId("55c87314fc92af6b6b2f9498"), "name" : "abc", "age" : "", "city" : "delhi" }
{ "_id" : ObjectId("55c87314fc92af6b6b2f9499"), "name" : "abc", "age" : "", "city" : "delhi" }
{ "_id" : ObjectId("55c87319fc92af6b6b2f949a"), "name" : "abc", "age" : 2, "city" : "delhi" }
{ "_id" : ObjectId("55c8731cfc92af6b6b2f949b"), "name" : "abc", "age" : 3, "city" : "delhi" }
{ "_id" : ObjectId("55c87320fc92af6b6b2f949c"), "name" : "abc", "age" : 4, "city" : "delhi" }
{ "_id" : ObjectId("55c87324fc92af6b6b2f949d"), "name" : "abc", "city" : "delhi" }
{ "_id" : ObjectId("55c87325fc92af6b6b2f949e"), "name" : "abc", "city" : "delhi" }
{ "_id" : ObjectId("55c87326fc92af6b6b2f949f"), "name" : "abc", "city" : "delhi" }

Here I suppose age type is Double.

You need to use the $exists and the $type operators. The first one to filter the documents where age is present and the latter where its value is not blank.

db.collection.aggregate([
    { "$match": { "name": "abc", "age": { "$exists": true, "$type": 1 }}}
])

The shorter way to write the above is:

db.collection.aggregate([
    { "$match": { "name": "abc", "age": { "$type": 1 }, "city": "delhi" }}
])

because the $type operator matches only if the field exists and is of the specified type.

---

If $match is the only pipeline operator in your aggregation then you don't need aggregation. Simply use the .find method.

db.collection.find({'name': 'abc', age: {$type: 1}, city: 'delhi' })

Output:

{ "_id" : ObjectId("55c87319fc92af6b6b2f949a"), "name" : "abc", "age" : 2, "city" : "delhi" }
{ "_id" : ObjectId("55c8731cfc92af6b6b2f949b"), "name" : "abc", "age" : 3, "city" : "delhi" }
{ "_id" : ObjectId("55c87320fc92af6b6b2f949c"), "name" : "abc", "age" : 4, "city" : "delhi" }