MongoDB聚合：将分数格式Rank转为数值数组并生成位置标签

Got it, let's solve this step by step. You're already halfway there with the $split — we just need to convert that string array to numbers, then apply your position labeling logic using conditional checks.

Full Aggregation Pipeline #

Here's the complete pipeline that handles both requirements:

db.yourCollection.aggregate([
  // Step 1: Split Rank into string array, then convert to numeric array
  {
    $addFields: {
      position_temp: {
        $map: {
          input: { $split: ["$Rank", "/"] },
          as: "rankStr",
          in: { $toInt: "$$rankStr" }
        }
      }
    }
  },
  // Step 2: Extract current rank and total ranks for easier conditional checks (optional but cleaner)
  {
    $addFields: {
      currentRank: { $arrayElemAt: ["$position_temp", 0] },
      totalRanks: { $arrayElemAt: ["$position_temp", 1] }
    }
  },
  // Step3: Generate the position label based on rules
  {
    $addFields: {
      position: {
        $switch: {
          branches: [
            { case: { $eq: ["$currentRank", 1] }, then: "第1名" },
            { case: { $eq: ["$currentRank", 2] }, then: "第2名" },
            { case: { $eq: ["$currentRank", 3] }, then: "第3名" },
            { case: { $eq: ["$currentRank", "$totalRanks"] }, then: "倒数第1名" },
            { case: { $eq: ["$currentRank", { $subtract: ["$totalRanks", 1] }] }, then: "倒数第2名" },
            { case: { $eq: ["$currentRank", { $subtract: ["$totalRanks", 2] }] }, then: "倒数第3名" }
          ],
          default: "Other"
        }
      }
    }
  },
  // Optional: Remove temporary fields if you don't need them
  {
    $project: {
      currentRank: 0,
      totalRanks: 0
    }
  }
])

Breakdown of Each Stage #

1. Convert Split String Array to Numeric:

   • We use $map to iterate over the string array from $split, and $toInt to convert each element to an integer. This gives you the position_temp array of numbers (e.g., ["5","8"] → [5,8]).

2. Extract Temporary Fields:

   • currentRank gets the first element of position_temp (your current rank number), totalRanks gets the second (total number of participants). This makes the conditional logic in the next stage much easier to read.

3. Generate Position Label:

   • $switch handles all the conditional cases:
     • Direct checks for top 3 ranks (1,2,3)
     • Checks for the last three ranks by comparing currentRank to totalRanks, totalRanks-1, totalRanks-2
     • Any other rank falls into the default case labeled "Other"

Example Outputs #

• For Rank: "1/8": position_temp: [1,8], position: "第1名"
• For Rank: "6/8": position_temp: [6,8], position: "倒数第3名"
• For Rank: "4/8": position_temp: [4,8], position: "Other"

内容的提问来源于stack exchange，提问作者Kamran Mushtaq