MongoDB Conditional Query

This chapter introduces the basic query syntax of MongoDB through the mongo shell. In the mongo shell, the db.collection.find() method is used to query data.

Prepare test data

Insert a few pieces of data into the inventory collection.

db.inventory.insertMany([
   { item: "journal", qty: 25, size: { h: 14, w: 21, uom: "cm" }, status: "A" },
   { item: "notebook", qty: 50, size: { h: 8.5, w: 11, uom: "in" }, status: "A" },
   { item: "paper", qty: 100, size: { h: 8.5, w: 11, uom: "in" }, status: "D" },
   { item: "planner", qty: 75, size: { h: 22.85, w: 30, uom: "cm" }, status: "D" },
   { item: "postcard", qty: 45, size: { h: 10, w: 15.25, uom: "cm" }, status: "A" }
]);

Query all documents

Query all documents in the inventory collection, {} represents an empty query condition.

db.inventory.find( {} )

Similar to the following SQL statement:

SELECT * FROM inventory

Equality query conditions

Syntax:

{ field: value, ... }

Example 1:

db.inventory.find( { status: "D" } )

Similar to the following SQL statement:

SELECT * FROM inventory WHERE status = "D"

Example 2: Nested field query, use a dot (.) to connect the fields of nested documents.

db.inventory.find( { "size.uom": "in" } )

Similar to the following SQL statement:

SELECT * FROM inventory WHERE size.uom = "in"

Note: MongoDB documents are stored in JSON format, so they support using nested fields as query conditions.

Using query operators

MongoDB supports a variety of query operators to implement various complex queries. Syntax:

{ <field1>: { <operator1>: <value1> }, ... }

Explanation:

• field1: field name
• operator1: operator
• value1: operator parameter

Example of the $in operator:

db.inventory.find( { status: { $in: [ "A", "D" ] } } )

Similar to the following SQL query with the "in" operator:

SELECT * FROM inventory WHERE status in ("A", "D")

Note: For details on operators, please refer to the subsequent MongoDB query details section.

and condition

Similar to SQL, MongoDB also supports the and condition.

Example:

// Enter multiple conditions consecutively, and connect conditions using and
db.inventory.find( { status: "A", item: "postcard"} )

Similar to the following SQL statement:

SELECT * FROM inventory WHERE status = "A" AND item = "postcard"

or condition

Similar to SQL, MongoDB also supports the or condition, which is implemented through the $or operator.

Syntax:

{
	$or: [
		{condition1},
		{condition2},
		.....
	]
}

Example:

db.inventory.find( { $or: [ { status: "A" }, { item: "postcard"} ] } )

Similar to the following SQL statement:

SELECT * FROM inventory WHERE status = "A" OR item = "postcard"

Combining and and or conditions

Similar to SQL, MongoDB can combine complex query conditions using and and or.

Example:

db.inventory.find( {
     status: "A",
     $or: [ { qty: { $lt: 30 } }, { item: /^p/ } ]
} )

Here, the $lt operator for less than and regular expression matching are used.

This is similar to the following SQL statement:

SELECT * FROM inventory WHERE status = "A" AND ( qty  < 30 or item like 'p%')

Tip: For a detailed explanation of MongoDB query conditions, please refer to the subsequent chapters.