MongoDB How to compare fields in a single document?

Question

Given the collection "example":

{_id: 1, prediction: "H", result: "A"}
{_id: 2, prediction: "H", result: "H"}
{_id: 3, prediction: "A", result: "A"}

What do I need to do to find records where the prediction and result values match? ie. documents 2 and 3?

Finding all the "H" predictions is:

db.example.find( { prediction: "H" } )

But I need to replace the literal "H" with the value in the result field of the same document.

Edit: Sorry, I should have said that I was using Mongo 3.6.

score 2 · Answer 1 · answered Feb 28 '18 at 09:37

You should be able to do this with an aggregation query, try out the $redact stage:

db.test.aggregate(
   [
     { $redact: {
        $cond: {
           if: { $eq: [ "$prediction", "$result" ] },
           then: "$$DESCEND",
           else: "$$PRUNE"
         }
       }
     }
   ]
);

This will yield the result:

{ "_id" : 2, "prediction" : "H", "result" : "H" }
{ "_id" : 3, "prediction" : "A", "result" : "A" }

More info on redact can be found here - https://docs.mongodb.com/manual/reference/operator/aggregation/redact/#redact-aggregation

score 0 · Accepted Answer · answered Feb 28 '18 at 09:36

0

You can use agregation

    db.example.aggregate(
       [
         {
           $project:
              {
                _id: 1,
                prediction: 1,
                result: 1,
                compare: { $eq: [ "$prediction", "$result" ] }
              }
         },
         {
           $match:
              {
                compare: true
              }
         }
       ]
    )

answered Feb 28 '18 at 09:36

Bùi Đức Khánh

3,975
6
27
43

1

This is very similar to another solution I found: db.example.find( { $expr: { $eq: ["$prediction", "$result"] } } ). I like these answers because they're efficient and exactly what I was looking for. – Spuggiehawk Feb 28 '18 at 10:21

score 0 · Answer 3 · answered Feb 28 '18 at 09:50

0

If you are using 3.6, this will work. refer this

db.getCollection('TEST').find( { $where: function() {
   return this.prediction == this.result
} })

Result :

{
    "_id" : 2,
    "prediction" : "H",
    "result" : "H"
}
{
    "_id" : 3,
    "prediction" : "A",
    "result" : "A"
}

answered Feb 28 '18 at 09:50

Rahul Sharma

9,534
1
15
37

1

It's advised to not use $where unless it's something that the aggregation pipeline doesn't support, as it will require the server to spin up the JavaScript engine to process the data. – Kevin Smith Feb 28 '18 at 10:05
1

@KevinSmith Thank you, I was not aware of that. – Rahul Sharma Feb 28 '18 at 10:10
However, this reminded me of a better way to do this! – Kevin Smith Feb 28 '18 at 10:13

MongoDB How to compare fields in a single document?

3 Answers3