CODEWITHSUNDEEP
mongodbaggregation-framework
Anjan Kailash
Anjan Kailash

Reputation: 63

$match stage is not giving any output in MongoDB aggregation

I need to print the documents with more than the average of one field in the same collection.

In the aggregation pipeline, I am not getting the avg_pmnt parameter available to $match stage (next stage)

For the following code 

db.payments.aggregate([
    {$group: {
        "_id":1, 
        "avg_pmnt": {$avg: "$AMOUNT"}, 
        "AMNT": {$push: "$AMOUNT"}
    } },
    {$unwind: "$AMNT"},
    {$project: {avg_pmnt: "$avg_pmnt", AMNT: "$AMNT"}},
])

I am getting the following output:

{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 14191 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 32642 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 33348 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 45864 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 82261 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 7565 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 44895 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 19502 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 47924 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 49524 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 50219 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 1491 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 17876 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 34638 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 101245 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 85411 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 11044 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 83598 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 6067 }
{ "_id" : 1, "avg_pmnt" : 32431.65567765568, "AMNT" : 14571 }

Type "it" for more

But I want to filter them

so, I tried this:

db.payments.aggregate([
    {$group: {
        "_id":1, 
        "avg_pmnt": {$avg: "$AMOUNT"}, 
        "AMNT": {$push: "$AMOUNT"}
    } },
    {$unwind: "$AMNT"},
    {$project: {avg_pmnt: "$avg_pmnt", AMNT: "$AMNT"}},
    {$match: {AMNT: {$gt: "$avg_pmnt"} }}
])

For which I am not getting any documents (empty set).

I also tried 

db.payments.aggregate([
    {$group: {
        "_id":1, 
        "avg_pmnt": {$avg: "$AMOUNT"}, 
        "AMNT": {$push: "$AMOUNT"}
    } },
    {$unwind: "$AMNT"},
    {$project: {avg_pmnt: "$avg_pmnt", AMNT: "$AMNT"}},
    {$match: {AMNT: {$gt: 1} }}
])

for which I am getting all the documents as the condition satisfies for every document.

Upvotes: 1

Views: 202

Answers (2)

Saravana
Saravana

Reputation: 12817

you can also use $redact to compare within the same document fields and filter the documents

db.payments.aggregate([ 
    {$group: { "_id":1, "avg_pmnt": {$avg: "$AMOUNT"}, "AMNT": {$push: "$AMOUNT"} } }, 
    {$unwind: "$AMNT"}, 
    {$project: {avg_pmnt: "$avg_pmnt", AMNT: "$AMNT"}}, 
    {$redact :{$cond : [{$gt: ["$AMNT", "$avg_pmnt" ]},"$$DESCEND","$$PRUNE"]}}
])

Upvotes: 2

mickl
mickl

Reputation: 49945

You should use $expr as you're trying to compare two fields inside of $match stage

db.payments.aggregate([ 
    {$group: { "_id":1, "avg_pmnt": {$avg: "$AMOUNT"}, "AMNT": {$push: "$AMOUNT"} } }, 
    {$unwind: "$AMNT"}, 
    {$project: {avg_pmnt: "$avg_pmnt", AMNT: "$AMNT"}}, 
    {$match: { $expr: { $gt: [ "$AMNT", "$avg_pmnt" ] } }  } 
])

Upvotes: 1

Related Questions