10

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.

Vishwas
  • 6,967
  • 5
  • 42
  • 69
Er Ajit Kumar
  • 113
  • 1
  • 2
  • 8
  • possible duplicate of [MongoDB: How to query for records where field is null or not set?](http://stackoverflow.com/questions/10591543/mongodb-how-to-query-for-records-where-field-is-null-or-not-set) – Neo-coder Aug 10 '15 at 07:35
  • You mean you don't want the 'age' "fieldname" in the results if is is blank or null, but to be there where it is not, don't you? i.e `{ "name": "abc", "city": "delhi" },{ "name": "def":, "age": 25, "city": "sydney" }` – Blakes Seven Aug 10 '15 at 07:46

2 Answers2

10

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}}]}} )
Vishwas
  • 6,967
  • 5
  • 42
  • 69
3

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" }
styvane
  • 59,869
  • 19
  • 150
  • 156