Introduction to MongoDB $cmp Operator

$cmp is a comparison operator in MongoDB used to compare two values and return the result of the comparison. The result is 0 if the two values are equal, -1 if the first value is less than the second value, and 1 if the first value is greater than the second value.

Syntax

The syntax of the $cmp operator is as follows:

{ $cmp: [ <expression1>, <expression2> ] }

Here, <expression1> and <expression2> are the two expressions to be compared, which can be any type of expressions, such as fields, constants, computed expressions, etc.

Use Cases

The $cmp operator can be used in various comparison scenarios, such as:

• Comparing the values of two fields, such as querying the information of students with top grades.
• Comparing the value of a field with a constant, such as querying sales records with sales exceeding 10000.
• Comparing the sizes of two dates, such as querying the order information in the past week.

Examples

Example 1: Comparing the values of two fields

Suppose we have a collection called students that contains a lot of student information, including student ID, name, and score. Now we need to query the information of the top three students based on their scores. We can use the $cmp operator and $sort restriction condition to query:

db.students.aggregate([
  {
    $project: {
      name: 1,
      score: 1,
      rank: { $cmp: ["$score", "$maxScore"] }
    }
  },
  {
    $sort: { rank: 1 }
  },
  {
    $limit: 3
  }
])

The query result is as follows:

{ _id: ObjectId("61f8033dc8d799a09f0b1a33"), name: 'Alice', score: 90, rank: 1 }
{ _id: ObjectId("61f8033dc8d799a09f0b1a32"), name: 'Lucy', score: 80, rank: 2 }
{ _id: ObjectId("61f8033dc8d799a09f0b1a31"), name: 'James', score: 70, rank: 3 }

Example 2: Comparing the value of a field with a constant

Suppose we have a collection called sales that contains a lot of sales record information, including sales ID, date, and amount. Now we need to query the sales records with sales exceeding 10000. We can use the $cmp operator to query:

db.sales.find({ $expr: { $gt: ["$amount", { $cmp: ["$amount", 10000] }] } })

The query result is as follows:

{ "_id" : ObjectId("61d952ec3f895900308f1be1"), "sale_id" : "20220101", "date" : ISODate("2022-01-01T00:00:00Z"), "amount" : 15000 }
{ "_id" : ObjectId("61d952ec3f895900308f1be2"), "sale_id" : "20220102", "date" : ISODate("2022-01-02T00:00:00Z"), "amount" : 20000 }
{ "_id" : ObjectId("61d952ec3f895900308f1be3"), "sale_id" : "20220103", "date" : ISODate("2022-01-03T00:00:00Z"), "amount" : 12000 }

As can be seen, the query results only return sales records with amounts greater than 10000.

Conclusion

The $cmp operator can compare the relative sizes of two values and return a number representing the relationship. It can be used in $expr expressions to perform complex query operations.