MongoDB group for documents containing arrays

Question

I have the following documents in my mongoDB collection.

{
  "_id" : ObjectId("51d90c3746503d6ab5933b88"),
  "scorearray" : [
    {
      "score" : 0.0
    }, 
    {
      "score" : 7.0
    }
    ],
    "player" : "Arod"
}

/* 1 */
{
  "_id" : ObjectId("51d90c0d46503d6ab5933b86"),
  "scorearray" : [
  {
      "score" : 2.0
  }, 
  {
      "score" : 1.0
  }, 
  {
      "score" : 5.0
  }],
  "player" : "Martini"
}

I am trying to get the max score for each player and I have a query as below , but it gives me just one player i.e Arod with 7.0 , I need both Arod 7.0 and Martini 5.0 in my query result .

Can some one please guide me on how to modify the below query.Any guidance would be greatly appreciated.

db.playerscorecollection.aggregate([
    {$project:{_id:0,player:1, "scorearray.score":1}},
    {$unwind:"$scorearray"},
    {$sort:{"scorearray.score":-1}},
    {$limit:1}
]);

Kay · Accepted Answer · 2013-07-09 14:34:15Z

2

you can use the $max operator in aggregation:

db.playerscorecollection.aggregate([
 { $unwind: "$scorearray"},
 { $group: { _id: "$player", maxScore: { $max: "$scorearray.score" } } }
])

----- For your second question to find for each player the max score and the venue at which that max score happened:

db.playerscorecollection.aggregate(
      { $unwind: "$scorearray"},
      { $group: { _id: { player: "$player", venue: "$scorearray.venue", score: "$scorearray.score" } } },
      { $sort: { "_id.score" : 1 } },
      { $group: { _id: "$_id.player", maxScore: { $last: "$_id.score" }, venue: { $last: "$_id.venue"} } }
)

edited Jul 9, 2013 at 14:34

answered Jul 8, 2013 at 18:18

Kay

3,01221 silver badges13 bronze badges

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

3 Comments

user1965449 Over a year ago

Thank you for an excellent answer , I just realized that I need to add venuealso to the score array , what if I need to get the max score for a player and the venue the game was played at also ?

user1965449 Over a year ago

Thank you for an excellent answer , I just realized that I need to add venue also to the score array , I need to get the max score for a player and the venue the game was played at also ? I tried adding venue to the group ,but I get an error that says "the group aggregate field 'venue' must be defined as an expression inside an object". Can you please help ?

user1965449 Over a year ago

Thank you for my second question I achieved it using the following simpler way instead.Now I need guidance in tranlating it to a Java program that uses a Java DB driver to retrieve data. Any guidance would be really appreciated. db.playerscorecollection.aggregate([ { $unwind: "$scorearray"}, { $group: { _id: {player:'$player,venue:'$venue'}, maxScore: { $max: "$scorearray.score" } } } ]);

user1965449 · Accepted Answer · 2013-07-11 01:46:49Z

0

Thank you for my second question, I achieved it using the following simpler way instead.

Now I need guidance in translating it to a Java program that uses a Java DB driver to retrieve data. Any guidance would be really appreciated.

db.playerscorecollection.aggregate([ 
{ $unwind: "$scorearray"}, 
{ $group: { _id: {player:'$player,venue:'$venue'}, 
maxScore: { $max: "$scorearray.score" } } } ]);

edited Jul 11, 2013 at 1:46

answered Jul 10, 2013 at 5:32

user1965449

2,9316 gold badges37 silver badges52 bronze badges

Collectives™ on Stack Overflow

MongoDB group for documents containing arrays

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related