1

I want to get records of a MongoDB collection that sticks to this condition:

fieldA > fieldB + someNaturalValue.

This is what I tried so far:

db.getCollection('collection').find({
$where: function() {
    return this.fieldA > this.fieldB + 10000} 
});
// or
db.getCollection('collection').aggregate([
    { "$project" : {
        "sum" : {"$add" : ["$fieldB", 10000]}
        }
    },
    { "$match" : {
        "sum" : {"$lte" : "$fieldA"}    
        }
    }
]);

The issue I face here is the extra value that I need to add in the condition to one of the fields.
Those are not working, that value is not taken into account.
What I am missing? I appreciate any kind of help.

Sample Data

db.collection.insert({fieldA : 21000, fieldB : 10000}); //1 ok
db.collection.insert({fieldA : 15000, fieldB : 8000});  //2 nok
db.collection.insert({fieldA : 24000, fieldB : 22000}); //3 nok
db.collection.insert({fieldA : 22000, fieldB : 1000});  //4 ok
Community
  • 1
  • 1
alexandrum
  • 429
  • 9
  • 17
  • 1
    Can you paste a small example dataset, in the form of `db.collection.insert` calls, so that we can play with it? – Sergio Tulentsev May 22 '17 at 10:04
  • I've tested both queries on my own sandbox and they both work fine. Even if fieldB is a string value – p.streef May 22 '17 at 10:06
  • Hi everyone. Thank you for fast replies. The answer from the indicated possible duplicate question works indeed, but it is using another function which I am fine with, but what about the alternative with **$where**? I would like to use this one too, because with aggregation I need to do an extra step to see the entire documents, not only the ids. Even so, should I accepted as duplicate only because, let's say, half of it is solved? – alexandrum May 22 '17 at 11:15
  • You can use `$redact` with aggregation. That's a single stage and since it's native operators in a single pass then it works much faster than using JavaScript with `$where`. Answers that quoted `$project` and then `$match` would have generally been written before `$redact` was available. – Neil Lunn May 22 '17 at 11:55
  • @alexandrum, It's just a teeny-tiny effort to adjust the solution to match your requirements. I have added a snippet, but you really could do it yourself. – Alex Blex May 22 '17 at 12:31
  • @AlexBlex, as I mentioned before, I adjusted it to my own case, but I did not know about **$replaceRoot** as in your snippet. Thanks for that and for your time. – alexandrum May 23 '17 at 06:00

1 Answers1

1

Adjusted code from the duplicated question:

db.collection.aggregate([
    {$project: {
        cmp_value: {$cmp: ['$fieldA', {$add: ['$fieldB', 10000]}]},
        obj: '$$ROOT'
    }},
    {$match: {cmp_value: {$gt: 0}}}, 
    { $replaceRoot: { newRoot: '$obj' } }
])

$where should be avoided where possible. Documentation is quite clear about it:

The $where provides greater flexibility, but requires that the database processes the JavaScript expression or function for each document in the collection

Alex Blex
  • 34,704
  • 7
  • 48
  • 75
  • I was just wondering what about this $where and why is there, even if the document says that is not that indicated to be used. It was more of a curiosity part of my question. Thanks for pointing this out, for now on I will try to avoid it. – alexandrum May 23 '17 at 06:03