Step Into MongoDB Query Operators

Posted on   |   In ,   |  

Comparison Operator

Operator JavaScript Example js Example Description
$eq == {field:{$eq:value}} a == b equal to a specified value(js: === )
$gt > {field:{$gt:value}} a > b greater than
$gte >= {field:{$gte:value}} a >= b greater than or equal to
$lt < {field:{$lt:value}} a < b less than
$lte <= {field:{$lte:value}} a <= b less than or equal to
$ne != {field:{$ne:value}} a != b not equal to
$in n/a {field:{$in:[, … ]}} n/a Matches any of the values specified in an array
$nin n/a {field:{$nin:[, … ]}} n/a Matches none of the values specified in an array

Examples

1
2
3
4
5
6
//return all account documents with name "Strong"
db.account.find({name:{$eq:"Strong"}}); 
//ruturn all accounts whose age are less than 18
db.account.find({age:{$lte:18}});		
//Return all documents in the inventory collection where the quantity does not equal 5 nor 15
db.inventory.find({quantity:{$nin:[5, 15]}});

Logical Operator

Operator JavaScript Example js Example Description
$and && {$and:[{},…{expressionN}]} x < a && x > b and, match the conditions of both clauses
$or OR {$or:[{},…{expressionN}]} x < a OR x > b or, match the conditions of either clause
$not ! {$not:[{},…{expressionN}]} !(x == y) not, do not match the query expression
$nor n/a {$nor:[{},…{expressionN}]} n/a Joins query clauses with a logical NOR returns all documents that fail to match both clauses

NOTE: OR stands for || for js opeator above.

$and example

$and performs a logical AND operation on an array of two or more expressions and selects the documents that satisfy all the expressions in the array

1
2
3
4
//AND query with multiple expressions 
db.inventory.find({$and:[{price:{$ne:1.99}},{price:{$exists:true}}]});
//implicit AND operation
db.inventory.find({price:{$ne:1.99,$exists:true}});

This query will select all documents in the inventory collection where:

  • the price field value is not equal to 1.99 and
  • the price field exists.

$nor example

1
2
//query with two expressions
db.inventory.find({$nor:[{price:19.99},{isSale:true}]});

This query will return all documents that:

  • contain the price field whose value is not equal to 19.99 and contain the isSale field whose value is not equal to true or
  • contain the price field whose value is not equal to 19.99 but do not contain the isSale field or
  • do not contain the price field but contain the isSale field whose value is not equal to true or
  • do not contain the price field and do not contain the isSale field

Field Check Operator

$exists

Matches documents that have the specified field, {field: {$exists:boolean value}} .
When boolean value is true, $exists matches the documents that contain the field, including documents where the field value is null. If boolean value is false, the query returns only the documents that do not contain the field.

1
db.inventory.find({quanity:{$exists:true, $nin : [5,15] }});

$type

$type selects the documents where the value of the field is an instance of the specified numeric BSON type.

1
db.collection.find({field:{$type:n});

NOTE:

n stands for numeric BSON type
1 for Double
2 for String
3 Object
4 Array
7 ObjectId
8 Boolean
9 Date
10 Null
11 Regular Expression
13 JavaScript
15 js with scope
16 32 bit integer
17 timestamp
18 64 bit integer
255 Min key(but query with -1)
127 Max key

Operator for Array

$all

Match arrays that contain all elements specified in the query
Like SQL’s in operator, the difference is that $all must match all values within [], but in can only match any one value within ().
{field:{$all:[value1, … valueN]}}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
// 
db.accounts.find({age : {$all : [16, 17]}});
// {name: 'Jobs', age: 26, age: [ 16, 18, 19 ] } : can't match
// {name: 'Bill', age: 26, age: [ 16, 17, 19 ] } : match
	
###elemMatch
The $elemMatch operator matches documents that contain an array field with at least one element that matches all the specified query criteria.
> Format: { <field>: { $elemMatch: { <q1>, <q2>, ... } } }
  It does not need touse $elemMatch operator for single query condition 

````bash
//collection scores
{ _id: 1, results: [ 82, 85, 88 ] }
{ _id: 2, results: [ 75, 88, 89 ] }
// match element 
db.scores.find( { results: { $elemMatch: { $gte: 80, $lt: 85 } } } )

//collection survey
{ _id: 1, results: [ { product: "abc", score: 10 }, { product: "xyz", score: 5 } ] }
{ _id: 2, results: [ { product: "abc", score: 8 }, { product: "xyz", score: 7 } ] }
{ _id: 3, results: [ { product: "abc", score: 7 }, { product: "xyz", score: 8 } ] }
//match embeded documents
db.survey.find({results:{$elemMatch:{product:"xyz", score:{$gte:8}}}});

$size

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//return all documents in collection where **field** is an array with N elements
db.collection.find({field:{$size:2}});
```

##More on query

###Null
understand it by a sample **employee** collection
> { "_id" : 1, "name" : "Strong", "age" : null }  // age is a NULL value
  { "_id" : 2, "name" : "Jacky", "age" : 33 }
  { "_id" : 3, "name" : "Garry", "addr" : 38 }	 // no age field

````bash
db.employee.find({age:null});  // return Garry & Strong
// only return Strong
db.employee.find({age:{$in:null,$exists:true}});

$where

$where provides flexibility
use the $where operator to pass either a string containing a JavaScript expression or a full JavaScript function to the query system
cannot take advantage of indexes

different expression, same result

1
2
3
4
5
6
7
db.employee.find({age:{$gt:35}});
//Reference the document in the JavaScript expression or function using either **this** or **obj**
db.employee.find({$where:"this.age > 35"}); 
db.employee.find("this.age>35");

fn = function() {return this.age > 35};
db.employee.find(fn);

sort

1
2
db.employee.find().sort({age:1});  //ascending sequence by age,  yourger first
db.employee.find().sor({age:-1});  //descending order by age, older first

skip & limit

1
db.employee.find().skip(10).limit(20); // return 20 records from the tenth record

$mod

Format: { field: { $mod: [ divisor, remainder ] } }

$regex

regular expression

Syntax:
{ : { $regex: /pattern/, $options: ‘‘ } }
{ : { $regex: ‘pattern’, $options: ‘‘ } }
{ : { $regex: /pattern/ } }

need more steps

$text

$text performs a text search on the content of the fields indexed with a text index.

Syntax: { $text: { $search: , $language: } }

need more steps