MongoDB聚合管道：通过分组和多个条件获取最小值

Question

请帮我为以下查询编写一个有效的 MongoDB 聚合管道：

在任何区域（包括“实验室”党）不超过 5 个党派有候选人，获得“实验室”党的最低选票。

这里是我编写的管道：

    pipeline = [
    // stage 1: unwind the nested results
    { $unwind: "$results" },

    // stage 2: group the results by area, get party count
    {
        $group: {
            _id: "$area",
            partyCount: {
                $sum: 1
            },
            results: {
                $push: {
                    party: "$results.party"
                    votes: "$results.votes"
                }
            }
        }
    },

    // stage 3: filter by the given party and partyCount <= 5
    {
        $match: {
            $and: [
                { partyCount: { $lte: 5 } },
                { results.party: "lab" }
            ]
        }
    },

    // stage 4: unwind the results again
    { $unwind: "$results" },

    // stage 5: filter the results now to only include 'lab' party
    {
        $match: { "results.party": "lab" }
    },

    // stage 6: project the results with the area name
    {
        $project: {
            name: "$_id",
            _id: 0,
            "results.party": 1,
            "results.votes": 1
        }
    },

    // stage 7: group the results by the party and get the minimum votes of the 'lab' party
    {
        $group: {
            _id: "$results.party",
            minVotes: {
                $min: "$results.votes"
            }
        }
    }]

我不确定这是否给了我正确的结果，并且我无法将区域名称也包含在最终结果中。

示例数据

        { "num": 27, "area": "basildon", "electors": 56793, "results": [ { "party": "con", "leader": "thatcher", "votes": 17516}, { "party": "lab", "leader": "foot", "votes": 16137}, { "party": "sdp", "leader": "jenkins", "votes": 11634}]}
    { "num": 28, "area": "basingstoke", "electors": 60414, "results": [ { "party": "con", "leader": "thatcher", "votes": 28381}, { "party": "lab", "leader": "foot", "votes": 10646}, { "party": "sdp", "leader": "jenkins", "votes": 15931}, { "party": "bnp", "leader": "tyndall", "votes": 344}]}
    { "num": 29, "area": "bassetlaw", "electors": 61807, "results": [ { "party": "con", "leader": "thatcher", "votes": 18400}, { "party": "lab", "leader": "foot", "votes": 22231}, { "party": "sdp", "leader": "jenkins", "votes": 8124}]}
    { "num": 30, "area": "bath", "electors": 62355, "results": [ { "party": "con", "leader": "thatcher", "votes": 22544}, { "party": "lab", "leader": "foot", "votes": 7259}, { "party": "sdp", "leader": "jenkins", "votes": 17240}, { "party": "eco", "leader": "whittaker", "votes": 441}]}
    { "num": 31, "area": "batley and spen", "electors": 71206, "results": [ { "party": "con", "leader": "thatcher", "votes": 21433}, { "party": "lab", "leader": "foot", "votes": 20563}, { "party": "sdp", "leader": "jenkins", "votes": 11678}, { "party": "eco", "leader": "whittaker", "votes": 493}]}

数据集位于此处。

Answer 1

您可以在聚合管道中执行以下操作：

1. $match 仅获取具有 lab
2. $addFields 2 个辅助字段的记录：
   1. partyCount：使用$size查找该区域的派对数量（假设没有重复的派对结果）
   2. minVote：使用$reduce有条件地查找实验室的最低投票
3. /code> 有条件地通过 partyCount

db.collection.aggregate([
  {
    $match: {
      "results.party": "lab"
    }
  },
  {
    "$addFields": {
      "partyCount": {
        $size: "$results"
      },
      "minVote": {
        "$reduce": {
          "input": "$results",
          "initialValue": null,
          "in": {
            "$cond": {
              "if": {
                $and: [
                  {
                    $eq: [
                      "$this.party",
                      "lab"
                    ]
                  },
                  {
                    $or: [
                      {
                        $eq: [
                          "$value",
                          null
                        ]
                      },
                      {
                        $lt: [
                          "$this.votes",
                          "$value"
                        ]
                      }
                    ]
                  }
                ]
              },
              "then": "$this.votes",
              "else": "$value"
            }
          }
        }
      }
    }
  },
  {
    "$match": {
      partyCount: {
        $lte: 5
      }
    }
  }
])

<= 5这里是 Mongo Playground 供您参考。