More advance search queries

Important note: This article is in relation to online MongoDB course. For more information about the course and other posts describing its content please check my main page here: M101P: MongoDB for Developers course. 

Operators in search queries

We can ask for values greater than.

 
> db.grades.find( {"student_id" : { $gt: 0, $lt:3 }, "type" : "quiz"} )
{ "_id" : ObjectId("50906d7fa3c412bb040eb57c"), "student_id" : 1, "type" : "quiz", "score" : 96.76851542258362 }
{ "_id" : ObjectId("50906d7fa3c412bb040eb580"), "student_id" : 2, "type" : "quiz", "score" : 1.528220212203968 }

We use the exists operator when we want to retrieve documents with specific attribute regardless of its value or type. The only condition is that this attribute needs to exists in the document.

 
> db.grades.insert( {"test" : 1, "test var" : "string one", "other var" : 2 } )
> db.grades.find( { "other var" : {$exists : true}  })
{ "_id" : ObjectId("52b7791fe7a1580719615d4c"), "test" : 1, "test var" : "string one", "other var" : 2 }

> db.grades.find( { "test var" : {$exists : true} })
{ "_id" : ObjectId("52b77870e7a1580719615d4a"), "test" : 1, "test var" : 1 }
{ "_id" : ObjectId("52b77877e7a1580719615d4b"), "test" : 1, "test var" : "string one" }
{ "_id" : ObjectId("52b7791fe7a1580719615d4c"), "test" : 1, "test var" : "string one", "other var" : 2 }

Finding documents with attribute of a particular type.

 
> db.grades.find( { "test var" : {$type : 1} })
{ "_id" : ObjectId("52b77870e7a1580719615d4a"), "test" : 1, "test var" : 1 }

> db.grades.find( { "test var" : {$type : 2} })
{ "_id" : ObjectId("52b77877e7a1580719615d4b"), "test" : 1, "test var" : "string one" }
{ "_id" : ObjectId("52b7791fe7a1580719615d4c"), "test" : 1, "test var" : "string one", "other var" : 2 }

You can use as well as regular expression when matching document attributes.

 
> db.grades.find( { "test var" : {$regex : "^s.*one$" }}  )
{ "_id" : ObjectId("52b77877e7a1580719615d4b"), "test" : 1, "test var" : "string one" }
{ "_id" : ObjectId("52b7791fe7a1580719615d4c"), "test" : 1, "test var" : "string one", "other var" : 2 }

If you want to provide 2 separate searching criterias you can use the $or operator.

 
> db.grades.find( { $or : [{ "test var" : {$type :1 }}, {"test var" : {$regex : "^s.*one$" }}] } )
{ "_id" : ObjectId("52b77870e7a1580719615d4a"), "test" : 1, "test var" : 1 }
{ "_id" : ObjectId("52b77877e7a1580719615d4b"), "test" : 1, "test var" : "string one" }
{ "_id" : ObjectId("52b7791fe7a1580719615d4c"), "test" : 1, "test var" : "string one", "other var" : 2 }

In a similar way the $and will help you to write queries where a single attribute needs to meet multiple criterias.

 
> db.grades.find( { $and : [ {"test var" : {$regex : "^s" }}, { "test var" : { $regex : "one$"} }] } )
{ "_id" : ObjectId("52b77877e7a1580719615d4b"), "test" : 1, "test var" : "string one" }
{ "_id" : ObjectId("52b7791fe7a1580719615d4c"), "test" : 1, "test var" : "string one", "other var" : 2 }

The $and queries can be further optimized and rewrite to use simpler syntax.

 
> db.grades.find( { "test var" : {$regex : "^s", $type : 1} } )
>
> db.grades.find( { "test var" : {$regex : "^s", $type : 2} } )
{ "_id" : ObjectId("52b77877e7a1580719615d4b"), "test" : 1, "test var" : "string one" }
{ "_id" : ObjectId("52b7791fe7a1580719615d4c"), "test" : 1, "test var" : "string one", "other var" : 2 }

It is important how do you write the search criterias above. If you write them separately you will create different query. The attribute criteria may be overwritten and only the last one will be used.

 
> db.grades.find( { "test var" : {$regex : "^s" }, "test var" : { $type : 1} } )
{ "_id" : ObjectId("52b77870e7a1580719615d4a"), "test" : 1, "test var" : 1 }

> db.grades.find( { "test var" : {$regex : "^rrrrrr" }, "test var" : { $type : 2} } )
{ "_id" : ObjectId("52b77877e7a1580719615d4b"), "test" : 1, "test var" : "string one" }
{ "_id" : ObjectId("52b7791fe7a1580719615d4c"), "test" : 1, "test var" : "string one", "other var" : 2 }