MongoDB : querying documents with two equal fields, $match and $eq

Question

What is the best way to return all documents in a collection if I want document.a == document.b?

I've tried

db.collection.aggregate([ { $match: { $eq: [ '$a', '$b' ] } }])

But it returns with no errors or results, because I assume it is literally matching strings "$a" and "$b". Is there a different way to specify that these are fields?

db.collection.aggregate([    { $project: { 
    eq: { $cond: [ { $eq: [ '$a', '$b' ] }, 1, 0 ] } 
} },
{ $match: { eq: 1 } }])

The above works, but requires the additional step of querying again with whatever documents it found or projecting all possible fields.

Is there a better way for achieving this query?

Answer 1

Basically, you are trying to perform a self join. An operation not supported by MongoDB.

Concerning the $eq operator, as you guessed:

• By design, the $eq comparison query operator match a field against a value.
• But the $eq comparison aggregation operator compare the value of two expressions.

I don't know any other way to perform what you need than using an extra $project step as you suggested.

Please note this is not significantly more expensive as, anyway, your query cannot use any index and MongoDB will do a full scan.

Answer 2

If I understood your question right you want those documents that have same values in field1 and field2.

For this try

db.coll.find({$where: function() { return this.field1 == this.field2 } } );

or more compact

db.coll.find({ $where : "this.field1 == this.field2" } );

Answer 3

I'm using AWS DocumentDb and $expr isn't supported (as of 16 May 2023). I used NatNgs's suggestion to use $addFields to compare two fields in the same document:

db.collection.aggregate($addFields: {
  "matchedValue" : {
    $cond: {
      if: {
        "$eq" : [ "$a", "$b" ]
      },
      then: "$a",
      else: false
    }
  }
});

This adds matchedValue field to the document but now we have a value we can filter on.

I used one more $match stage to filter out the false values.