MongoDB : aggregation framework : $match between fields

Question

I have a test collection with two documents :

> db.test.find().pretty()
{ "_id" : ObjectId("510114b46c1a3a0f6e5dd7aa"), "a" : 1, "b" : 2 }
{ "_id" : ObjectId("510114c86c1a3a0f6e5dd7ab"), "a" : 3, "b" : 1 }

With aggregation framework, I want to get only the documents where a is greater than b. $gt get only values in argument not fields...

> db.test.aggregate([{"$match":{"$a":{"$gt":"$b"}}}])
{ "result" : [ ], "ok" : 1 } /* don't work*/

Do you have some ideas ?

Thanks in advance

Best regards

It seems you made an error in your aggregation query. The field you wish to match should be specified as a, not $a, i.e. db.test.aggregate([{"$match":{"a":{"$gt":"$b"}}}]) — Quolonel Questions
– Quolonel Questions, Commented Feb 10, 2016 at 10:30
@QuolonelQuestions, that still won't work, MongoDB won't use the value of the field b, it will use $b as the right hand side of the comparison. This can be tested by executing db.test.aggregate([{"$match":{"a":{"$eq":"$a"}}}]). Zero results will be returned. — Paul
– Paul, Commented Feb 7, 2017 at 19:21
You can use aggregation expression in regular query in 3.6. Something like db.test.find( {"$expr": {"$gt": ["$a", "$b"]}}) and in aggregation thru db.test.aggregate( {"$match":{"$expr": {"$gt": ["$a", "$b"]}}}) — s7vr
– s7vr, Commented Dec 13, 2017 at 21:48

Sammaye · Accepted Answer · 2013-01-24 12:43:16Z

44

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.

answered Jan 24, 2013 at 12:43

Sammaye

44k7 gold badges110 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sankar muniyappa Over a year ago

May i know how to use above mongdb query with php language. I've used above method not works. Please help on this.

Sammaye Over a year ago

@shankarmsr With the new driver:

(new \MongoDB\Client())->db->collection->aggregate([['$project' => ['cmp_value' => ['$cmp' => ['$a', '$b']]]], ['$match' => ['cmp_value' => ['$gt' => 0]]]])

or something like that

sankar muniyappa Over a year ago

Let me check with your answer.Hope it will works fine

Govind Rai · Accepted Answer · 2019-06-22 14:36:43Z

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 }

Collectives™ on Stack Overflow

MongoDB : aggregation framework : $match between fields

2 Answers 2

3 Comments

Use the `$expr` operator.

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Use the $expr operator.

Comments

Your Answer

Sign up or log in

Post as a guest

Use the `$expr` operator.