MongoDB：投票的聚合/分組

我正在嘗試創建投票結果聚合

我有兩個收藏

民意調查- 這是一份檔案

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

投票- 這是一份檔案

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

我想執行聚合查詢以獲取投票結果

所以我正在做以下查詢

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" }
            }
        }
    ]
)

這給了我這種形式的結果

{ _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 }

所以我對什么感興趣，我想讓結果看起來像（比如合并選項和選項組）

{ _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 }

另一個問題是資料庫結構總體上可以接受還是我可以用更好的方式來表示？

uj5u.com熱心網友回復：

一種選擇是先分組并使用$lookup后者，以便從poll集合中獲取更少的資料。之后$lookup，使用$mapwith$cond合并陣列：

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"
  }}
])

看看它在操場上的例子是如何作業的

標籤：mongodbmongodb查询聚合框架

上一篇：Mongodb和nodejs查找和過濾嵌套物件

下一篇：MAUI：無法將相機捕獲的影像轉換為byte[]陣列以將其發送到mongodb