How to return documents where two fields have same value [duplicate]

Question

Is it possible to find only those documents in a collections with same value in two given fields?

{
    _id:    'fewSFDewvfG20df', 
    start:  10,
    end:    10
}

As here start and end have the same value, this document would be selected.

I think about something like...

Collection.find({ start: { $eq: end } })

... which wouldn't work, as end has to be a value.

Answer 1

You have two options here. The first one is to use the $where operator.

Collection.find( { $where: "this.start === this.end" } )

The second option is to use the aggregation framework and the $redact operator.

Collection.aggregate([
    { "$redact": { 
        "$cond": [
            { "$eq": [ "$start", "$end" ] },
            "$$KEEP",
            "$$PRUNE"
        ]
    }}
])

Which one is better?

The $where operator does a JavaScript evaluation and can't take advantage of indexes so query using $where can cause a drop of performance in your application. See considerations. If you use $where each of your document will be converted from BSON to JavaScript object before the $where operation which, will cause a drop of performance. Of course your query can be improved if you have an index filter. Also There is security risk if you're building your query dynamically base on user input.

The $redact like the $where doesn't use indexes and even perform a collection scan, but your query performance improves when you $redact because it is a standard MongoDB operators. That being said the aggregation option is far better because you can always filter your document using the $match operator.

$where here is fine but could be avoided. Also I believe that you only need $where when you have a schema design problem. For example adding another boolean field to the document with index can be a good option here.

Answer 2

You can use $expr in mongodb 3.6 to match the two fields from the same document.

db.collection.find({ "$expr": { "$eq": ["$start", "$end"] } })

or with aggregation

db.collection.aggregate([
  { "$match": { "$expr": { "$eq": ["$start", "$end"] }}}
])