如何在MongoDB中编写等价于SELECT * FROM table t WHERE t.col1=t.col2的查询

Hey there! Let's turn your SQL query into the right MongoDB syntax. Your original SQL is looking for all rows where col1 equals col2—here's how to do that in MongoDB:

Basic Find Query #

MongoDB uses the find() method for reading documents, and to compare two fields within the same document, you need to use the $expr operator (since regular query filters only compare fields to constant values, not other fields).

Here's the exact equivalent:

db.table.find({
  $expr: { $eq: ["$col1", "$col2"] }
})

Breakdown of the Syntax #

• db.table refers to your collection (equivalent to the SQL table table).
• $expr lets you use aggregation expressions inside a query filter.
• $eq is the aggregation operator for equality checks, and we pass the two field names (prefixed with $) as arguments to compare them.

Alternative: Using Aggregation Pipeline #

If you're working within an aggregation pipeline (for more complex operations later), you can use the same condition in a $match stage:

db.table.aggregate([
  {
    $match: {
      $expr: { $eq: ["$col1", "$col2"] }
    }
  }
])

This will return all documents where the values of col1 and col2 are identical, just like your original SQL query does.

内容的提问来源于stack exchange，提问作者Sagar