Am trying to create a poll results aggregation

I have two collections

poll - here is one document

{
  "_id": {
    "$oid": "636027704f7a15587ef74f26"
  },
  "question": "question 1",
  "ended": false,
  "options": [
    {
      "id": "1",
      "option": "option 1"
    },
    {
      "id": "2",
      "option": "option 2"
    },
    {
      "id": "3",
      "option": "option 3"
    }
  ]
}

Vote - here is one document

{
  "_id": {
    "$oid": "635ed3210acbf9fd14af8fd1"
  },
  "poll_id": "636027704f7a15587ef74f26",
  "poll_option_id": "1",
  "user_id": "1"
}

and i want to perform an aggregate query to get poll results

so am doing the following query

db.vote.aggregate(
    [
        {
            $addFields: {
                poll_id: { "$toObjectId": "$poll_id" }
            },
        },
        {
            $lookup: {
                from: "poll",
                localField: "poll_id",
                foreignField: "_id",
                as: "details"
            }
        },
        {
            $group:
            {
                _id: { poll_id: "$poll_id", poll_option_id: "$poll_option_id" },
                details: { $first: "$details" },
                count: { $sum: 1 }
            }
        },
        {
            $addFields: {
                question: { $arrayElemAt: ["$details.question", 0] }
            }
        },
        {
            $addFields: {
                options: { $arrayElemAt: ["$details.options", 0] }
            }
        },
        {
            $group: {
                _id: "$_id.poll_id",
                poll_id: { $first: "$_id.poll_id" },
                question: { $first: "$question" },
                options: { $first: "$options" },
                optionsGrouped: {
                    $push: {
                        id: "$_id.poll_option_id",
                        count: "$count"
                    }
                },
                count: { $sum: "$count" }
            }
        }
    ]
)

That is giving me this form of results

{ _id: ObjectId("636027704f7a15587ef74f26"),
  poll_id: ObjectId("636027704f7a15587ef74f26"),
  question: 'question 1',
  options: 
   [ { id: '1', option: 'option 1' },
     { id: '2', option: 'option 2' },
     { id: '3', option: 'option 3' } ],
  optionsGrouped: 
   [ { id: '1', count: 2 },
     { id: '2', count: 1 } ],
  count: 3 }

So what am interested in i want to have the results looking like ( like merging both options & options Group)

{ _id: ObjectId("636027704f7a15587ef74f26"),
  poll_id: ObjectId("636027704f7a15587ef74f26"),
  question: 'question 1',
  optionsGrouped: 
   [ { id: '1', option: 'option 1', count: 2 },
     { id: '2', option: 'option 2', count: 1 },
     { id: '3', option: 'option 3', count: 0 } ],
  count: 4 }

Another question is the DB structure acceptable overall or i can represent that in a better way ?

CodePudding user response：

One option is to group first and use the $lookup later, in order to fetch less data from the poll collection. After the $lookup, use $map with $cond to merge the arrays:

db.vote.aggregate([
  {$group: {
      _id: {poll_id: {$toObjectId: "$poll_id"}, poll_option_id: "$poll_option_id"},
      count: {$sum: 1}
  }},
  {$group: {
      _id: "$_id.poll_id",
      counts: {
        $push: {count: "$count", option: {$concat: ["option ", "$_id.poll_option_id"]}}
      },
      countAll: {$sum: "$count"}
  }},
  {$lookup: {
      from: "poll",
      localField: "_id",
      foreignField: "_id",
      as: "poll"
  }},
  {$project: {poll: {$first: "$poll"}, counts: 1, countAll: 1}},
  {$project: {
      optionsGrouped: {
        $map: {
          input: "$poll.options",
          in: {$mergeObjects: [
              "$$this",
              {$cond: [
                  {$gte: [{$indexOfArray: ["$counts.option", "$$this.option"]}, 0]},
                  {$arrayElemAt: ["$counts", {$indexOfArray: ["$counts.option", "$$this.option"]}]},
                  {count: 0}
              ]}
          ]}
        }
      },
      count: "$countAll",
      question: "$poll.question"
  }}
])

See how it works on the playground example