CODEWITHSUNDEEP
mongodbmongooseaggregation-framework
Sandeep Nair
Sandeep Nair

Reputation: 157

Mongodb aggregates, getting confused with group by

I have a collection in mongo that has values like this

{ id: Mongo ID,
  Match_Type: Challenge Match,
  Match_Number: 0001
  Challenging_Player: Mark,
  Challenged_Player: John,
}
{ id: Mongo ID,
  Match_Type: Challenge Match,
  Match_Number: 0002
  Challenging_Player: John,
  Challenged_Player: Bob
}

The names can appear in either of the 2 columns, either Challenging Player or Challenged Player. I am trying to do an aggregate to find out the player who has played the maximum number of Challenge matches. Which means I need a group by with Distinct count on both the Challenging and the challenged player.

Can someone please help?

Upvotes: 0

Views: 91

Answers (2)

Ana Lava
Ana Lava

Reputation: 787

db.getCollection('the_collection').aggregate([
  {$match: { Match_Type: "Challenge Match"}},
  { $project: { players: { $setUnion: [ ["$Challenging_Player"], ["$Challenged_Player"] ] } }},
  { "$unwind" : "$players"},
  {$group : { _id:"$players", count:{$sum:1} } }
  ])

Upvotes: 2

Kavithakaran Kanapathippillai
Kavithakaran Kanapathippillai

Reputation: 8213

   db.<yourcollection>.aggregate( [
     { $project  : { players: ["$Challenging_Player", "$Challenged_Player"]}},
     { "$unwind" : "$players"},
     { "$group"  : {"_id" : "$players", "count" : {"$sum" : 1}}}
   ] )

I am unwinding each of your document into 2 documents one for each player and then group by that field

Upvotes: 1

Related Questions