I'm trying to build a polling system. I have a collection of choices and a collection of votes

Choices collection, showing the choices available in 'poll1'

_id:'1',
pollId:'poll1',
choice:'red'

_id:'2',
pollId:'poll1',
choice:'blue'

_id:'3',
pollId:'poll1'
choice:'green'

Votes collection

_id:'a'
choiceId:'3'  // corresponds to the _id in the Choices collection
userId:'user1'  // id of the user who voted 

_id:'b'
choiceId:'3'
userId:'user2'

_id:'c'
choiceId:'1'
userId:'user3'

I would like to be able to count the number of votes for each choice in a poll. At the moment I have the following basic query on the Votes collection which returns just the id of the choice and the number of votes

[
    {
        '$match': {
            'pollId': pollId
        }
    }, {
        '$group': {
             '_id': '$choiceId', 
             'votes': {
                 '$sum': 1
             }
         }
    }
]

However, I would like to be able to use a lookup on the Choices collection as a reference - for every available choice in a poll, get the choiceId, the choiceName, and a count of the number of votes for that choice.

From reading the docs it would seems to be something like this, but it just returns an array of all the choices for that poll, and a total count of the votes, not the votes for each choice

[
   {
       '$match': {
           'pollId': pollId
       }
   }, {
       '$lookup': {
            'from': 'choices', 
            'localField': 'pollId', 
            'foreignField': 'pollId', 
            'as': 'choiceData'
        }
   }, {
       '$group': {
           '_id': '$choiceData._id', 
           'votes': {
               '$sum': 1
           }
       }
    }
 ]

Could anyone please explain how to get data in the following sort of structure:

choiceId:'3', choice:'green', votes:2

choiceId:'1', choice:'red', votes:1

choiceId:'2', choice:'blue', votes:0

Any help would be sincerely appreciated. Cheers, Matt

CodePudding user response：

You can use this aggregation query:

• First $match by your poll name (as you do).
• Then use $lookup to merge collections. But here there are a few thing to do better: from is the other collection, localField is the field from the collection who is doing the lookup and foreignField is the other collection. So you have to join with votes collection where the field _id in choices collection is the same as choiceId in votes collection.
• And last $project to get the values you want. In this case set choiceId as the _id and votes as the number of results returned by lookup (i.e. the number of votes).

db.choices.aggregate([
  {
    "$match": {
      "pollId": "poll1"
    }
  },
  {
    "$lookup": {
      "from": "votes",
      "localField": "_id",
      "foreignField": "choiceId",
      "as": "choiceData"
    }
  },
  {
    "$project": {
      "_id": 0,
      "choice": 1,
      "choiceId": "$_id",
      "votes": {
        "$size": "$choiceData"
      }
    }
  }
])

Example here