CODEWITHSUNDEEP
mongodb
jabclab
jabclab

Reputation: 15042

Obtain count of multiple Arrays in same MongoDB query

If I have the following players collection:

> db.players.findOne()
{
    _id: 1,
    goals: ['goal_a', 'goal_b', 'goal_c'],
    matches: ['match_a', 'match_b', 'match_c', 'match_d']
}

Is it possible in MongoDB to write a query which will return a count of each Array, e.g. for _id: 1:

{
    _id: 1, num_goals: 3, num_matches: 4
}

I've so far been able to obtain a count for one of the Arrays using the aggregation pipeline:

> db.players.aggregate(
      { $match: { '_id': 1 } }, 
      { $unwind: '$goals' }, 
      { $group: { _id:'player', num_goals: {$sum:1} } }
  )

Is it possible to extend this concept to count multiple Arrays in the same query?

Thanks :-)

Upvotes: 0

Views: 330

Answers (1)

Kenneth
Kenneth

Reputation: 300

Your question caught my interest! And I decided to play around a little bit, and it is possible! Still a little bit tricky though.

Using multiple $groups and $unwinds you can reshape the document in the pipeline and use that to get to the desired output! The query I tested - which worked with your document structure - is as follows:

 > db.players.aggregate(
      {$match:{"_id":1}},
      {$unwind: "$matches"},
      {$group:{_id:"$_id",num_matches :{$sum:1},goals:{$first:"$goals"}}},
      {$unwind:"$goals"},
      {$group:{"_id":"$_id",num_matches:{$first:"$num_matches"},num_goals:{$sum:1}}}
   )

What it does is repeat the step you already discovered twice, since we're handling two arrays with a simple addition. In the second $group statement I included the number of matches that was part of the output of the first statement!

Here's the output:

The output

Hope this helps!

Upvotes: 2

Related Questions