MongoDB : aggregation framework : $match between fields
Hmm without much testing on my end I will say you can use $cmp
for this:
http://docs.mongodb.org/manual/reference/aggregation/cmp/#_S_cmp
db.test.aggregate([
{$project: {
// All your other fields here
cmp_value: {$cmp: ['$a', '$b']}
}},
{$match: {cmp_value: {$gt: 0}}}
])
There might be a better way but I haven't got a MongoDB installation near me to test.
Use the $expr
operator.
Introduced in version 3.6, $expr
can build query expressions that compare fields from the same document.
Compare Two Fields from A Single Document (example taken directly from MongoDB Docs):
Consider an monthlyBudget collection with the following documents:
{ "_id" : 1, "category" : "food", "budget": 400, "spent": 450 }
{ "_id" : 2, "category" : "drinks", "budget": 100, "spent": 150 }
{ "_id" : 3, "category" : "clothes", "budget": 100, "spent": 50 }
{ "_id" : 4, "category" : "misc", "budget": 500, "spent": 300 }
{ "_id" : 5, "category" : "travel", "budget": 200, "spent": 650 }
The following operation uses $expr
to find documents where the spent amount exceeds the budget:
db.monthlyBudget.find( { $expr: { $gt: [ "$spent" , "$budget" ] } } )
The operation returns the following results:
{ "_id" : 1, "category" : "food", "budget" : 400, "spent" : 450 }
{ "_id" : 2, "category" : "drinks", "budget" : 100, "spent" : 150 }
{ "_id" : 5, "category" : "travel", "budget" : 200, "spent" : 650 }