CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Alex
Alex

Reputation: 37

How can i display count of related documents on parent level?

I'm trying to build a voting system where you can have X num of options to vote for on an entry. The query I'm building now is when retrieving an entry, I would like to get the numbers of votes per option on an entry.

I have a very clear understanding of how I would do this in SQL but grasping to understand the concepts of aggregation, lookup, and group in MongoDB

The model looks like this:

Entries

{
  "_id": "5fc2765938401a2308e18ac5",
  "options": [
      {
          "name": "First Option"
          "_id": "5fc2765938401a2308e18ac6",
      },
      {
          "name": "Second Option"
          "_id": "5fc2765938401a2308e18are",
      },
      {
          "name": "Third Option"
          "_id": "5fc2765938401a2308e18aef",
      },
  ],
},
{
  "_id": "5fc2766438401a2308e18ac8",
  "options": [
      {
          "name": "Some other option"
          "_id": "5fc2766438401a2308e18ac9",
      },
      {
          "_id": "5fc2766438401a2308e18aca",
          "name": "This is also an option"
      }
  ],
}

Votes

{
  "_id": "5fc2765938401a2308e18ac5",
  "entryId": "5fc2765938401a2308e18ac6"
},
{
  "_id": "5fc2765938401a2308e18aer",
  "entryId": "5fc2765938401a2308e18are"
},
{
  "_id": "5fc2765938401a2308e18ek",
  "entryId": "5fc2765938401a2308e18ac6"
}
...

And I want the results of Entry to look like this.

{
  "_id": "5fc2765938401a2308e18ac5",
  "options": [
      {
          "name": "First Option"
          "_id": "5fc2765938401a2308e18ac6",
          "votes": 1,
      },
      {
          "name": "Second Option"
          "_id": "5fc2765938401a2308e18are",
          "votes": 0,
      },
      {
          "name": "Third Option"
          "_id": "5fc2765938401a2308e18aef",
          "votes": 5,
      },
  ],
},
{
  "_id": "5fc2766438401a2308e18ac8",
  "options": [
      {
          "name": "Some other option"
          "_id": "5fc2766438401a2308e18ac9",
          "votes": 3,
      },
      {
          "_id": "5fc2766438401a2308e18aca",
          "name": "This is also an option"
          "votes": 10,
      }
  ],
}

Upvotes: 1

Views: 49

Answers (1)

turivishal
turivishal

Reputation: 36104

  • $lookup to join votes collection, pass local field optoins._id and foreign field entryId
  • $project get options votes, $map to iterate loop of options array, $filter to get matching entryId records and $size to get count of element in return array, merge votes field and current object using $mergeObjects
db.entries.aggregate([
  {
    $lookup: {
      from: "votes",
      localField: "options._id",
      foreignField: "entryId",
      as: "votes"
    }
  },
  {
    $project: {
      options: {
        $map: {
          input: "$options",
          as: "a",
          in: {
            $mergeObjects: [
              "$$a",
              {
                votes: {
                  $size: {
                    $filter: {
                      input: "$votes",
                      cond: { $eq: ["$$this.entryId", "$$a._id"] }
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Playground

Upvotes: 1

Related Questions