Question 116318 in Mongodb Question posted in
The official documentation can be found here.

MongoDB: Aggregation/Grouping for poll votes

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 ?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I had reworked the query to match my desires

    and this query is achieving the question i have asked

    db.poll.aggregate([
  {
    $addFields: {
      _id: {
        $toString: "$_id"
      }
    }
  },
  {
    $lookup: {
      from: "poll_vote",
      localField: "_id",
      foreignField: "poll_id",
      as: "votes"
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $let: {
          vars: {
            count: {
              $size: "$votes"
            },
            options: {
              $map: {
                input: "$options",
                as: "option",
                in: {
                  $mergeObjects: [
                    "$$option",
                    {
                      count: {
                        $size: {
                          $slice: [
                            {
                              $filter: {
                                input: "$votes",
                                as: "v",
                                cond: {
                                  $and: [
                                    {
                                      $eq: [
                                        "$$v.poll_option_id",
                                        "$$option._id"
                                      ]
                                    }
                                  ]
                                }
                              }
                            },
                            0,
                            100
                          ]
                        }
                      }
                    },
                    {
                      checked: {
                        $toBool: {
                          $size: {
                            $slice: [
                              {
                                $filter: {
                                  input: "$votes",
                                  as: "v",
                                  cond: {
                                    $and: [
                                      {
                                        $eq: [
                                          "$$v.user_id",
                                          2
                                        ]
                                      },
                                      {
                                        $eq: [
                                          "$$v.poll_option_id",
                                          "$$option._id"
                                        ]
                                      }
                                    ]
                                  }
                                }
                              },
                              0,
                              100
                            ]
                          }
                        }
                      }
                    }
                  ]
                }
              }
            }
          },
          "in": {
            _id: "$_id",
            question: "$question",
            count: "$$count",
            ended: "$ended",
            options: "$$options"
          }
        }
      }
    }
  },
  {
    $addFields: {
      answered: {
        $reduce: {
          input: "$options",
          initialValue: false,
          in: {
            $cond: [
              {
                $eq: [
                  "$$this.checked",
                  true
                ]
              },
              true,
              "$$value"
            ]
          }
        }
      }
    }
  }
])

  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search