Chapter 3: MongoDB - Conditional Query

This chapter introduces the conditional query of MongoDB, which is similar to the comparison operators such as equal, greater than, and less than in SQL statements.

Conditional Operators Supported by MongoDB

Test Data

The inventory collection data is as follows

{ _id: 1, item: { name: "ab", code: "123" }, qty: 15, tags: [ "A", "B", "C" ] }
{ _id: 2, item: { name: "cd", code: "123" }, qty: 20, tags: [ "B" ] }
{ _id: 3, item: { name: "ij", code: "456" }, qty: 25, tags: [ "A", "B" ] }
{ _id: 4, item: { name: "xy", code: "456" }, qty: 30, tags: [ "B", "A" ] }
{ _id: 5, item: { name: "mn", code: "000" }, qty: 20, tags: [ [ "A", "B" ], "C" ] }

$eq (Equal Match)

db.inventory.find( { qty: { $eq: 20 } } )
db.inventory.find( { "item.name": { $eq: "ab" } } )

// Shortened, can ignore the $eq operator, qty = 20
db.inventory.find( { qty: 20 } )

Equivalent SQL:

select * from inventory where qty = 20
// This is just an example, SQL does not support the format of nested fields like item.name
select * from inventory where item.name = 20

$gt (Greater Than)

db.inventory.find( { qty: { $gt: 20 } } )

Equivalent SQL:

select * from inventory where qty > 20

$gte (Greater Than or Equal To)

db.inventory.find( { qty: { $gte: 20 } } )

Equivalent SQL:

select * from inventory where qty >= 20

$in

Matches one value in the array

db.inventory.find( { qty: { $in: [ 5, 15 ] } } )

Equivalent SQL:

select * from inventory where qty in (5, 15)

$nin

Opposite to the $in operator

db.inventory.find( { qty: { $nin: [ 5, 15 ] } } )

Equivalent SQL:

select * from inventory where qty not in (5, 15)

$lt (Less Than)

db.inventory.find( { qty: { $lt: 20 } } )

Equivalent SQL:

select * from inventory where qty < 20