CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
user2772032
user2772032

Reputation:

filter records from a collection having max value of a particular column using aggregation in mongodb

I want to fetch "all the documents" having highest value for specific field and than group by another field.

Consider below data:

_id:1, type:debug, status:Pass, build:13,  params:abc,  time:1
_id:2, type:debug, status:Pass, build:10,  params:abc,  time:2
_id:3, type:test,  status:Pass, build:13,  params:xyz,  time:3
_id:4, type:debug, status:Pass, build:12,  params:abc,  time:4
_id:5, type:debug, status:Pass, build:13,  params:xyz,  time:5
_id:6, type:debug, status:NA,   build:13,  params:abc,  time:6
_id:7, type:debug, status:Pass, build:13,  params:abc,  time:6
_id:8, type:test,  status:Fail, build:13,  params:xyz,  time:6
_id:9, type:debug, status:NA,   build:13,  params:pqr,  time:6

I want - first filter data with status Pass or Fail - then filter from the step1 data with max build value(which is 13 here) - then group by params with average of time

Expected Output:

params:abc  time:3.5
params:xyz  time:4.67

Upvotes: 1

Views: 133

Answers (1)

Saravana
Saravana

Reputation: 12817

For this, you can find the max id, lookup max id in the same collection filtering matching max id,and do group aggregation to get the desired results on the matched documents

pipeline

db.e2e.aggregate(
    [
        {$group   : {_id : null, maxBuild : {$max : "$build"}}},
        {$lookup  : {from : "e2e", localField : "maxBuild", foreignField : "build", as : "builds"}},
        {$unwind  : "$builds"},
        {$group   : {_id : {build : "$builds.build", params : "$builds.params"}, time : {$avg : "$builds.time"}}},
        {$project : {_id : 0, build : "$_id.build", params : "$_id.params", time : "$time"}}
    ]
).pretty()

result

{ "build" : 13, "params" : "xyz", "time" : 4 }
{ "build" : 13, "params" : "abc", "time" : 3.5 }
>

EDIT

you need to add $match stage to filter

db.e2e.aggregate(
    [
        {$group   : {_id : null, maxBuild : {$max : "$build"}}},
        {$lookup  : {from : "e2e", localField : "maxBuild", foreignField : "build", as : "builds"}},
        {$unwind  : "$builds"},
        {$match   : {"builds.status" : {$ne : "NA"}}},
        {$group   : {_id : {build : "$builds.build", params : "$builds.params"}, time : {$avg : "$builds.time"}}},
        {$project : {_id : 0, build : "$_id.build", params : "$_id.params", time : "$time"}}
    ]
).pretty()

Upvotes: 1

Related Questions