MongoDB聚合需求：统计旅行者城市访问次数及预算均值

Got it, let's break down how to build the aggregation pipeline you need. We have two core tasks here: counting how many times each traveler visited each city, and calculating the average of the Budget values from the variables array—then formatting everything to match your desired output.

Here's the complete aggregation pipeline, followed by a step-by-step explanation:

db.Traveler.aggregate([
  // Step 1: Unwind the cities array to count individual visits
  {
    $unwind: "$cities"
  },
  // Step 2: Group by traveler + city to count visits, retain core fields
  {
    $group: {
      _id: {
        travelerId: "$_id",
        cityId: "$cities._id",
        cityName: "$cities.name"
      },
      _class: { $first: "$_class" },
      name: { $first: "$name" },
      startTimestamp: { $first: "$startTimestamp" },
      endTimestamp: { $first: "$endTimestamp" },
      source: { $first: "$source" },
      visited: { $sum: 1 },
      variables: { $first: "$variables" }
    }
  },
  // Step 3: Re-group by traveler to rebuild the cities array with visit counts
  {
    $group: {
      _id: "$_id.travelerId",
      _class: { $first: "$_class" },
      name: { $first: "$name" },
      startTimestamp: { $first: "$startTimestamp" },
      endTimestamp: { $first: "$endTimestamp" },
      source: { $first: "$source" },
      cities: {
        $push: {
          _id: "$_id.cityId",
          name: "$_id.cityName",
          visited: "$visited"
        }
      },
      variables: { $first: "$variables" }
    }
  },
  // Step 4: Isolate the Budget variable from the variables array
  {
    $addFields: {
      budgetVar: {
        $first: {
          $filter: {
            input: "$variables",
            cond: { $eq: ["$$this.name", "Budget"] }
          }
        }
      }
    }
  },
  // Step 5: Calculate the average Budget value (convert strings to numbers first)
  {
    $addFields: {
      avgBudget: {
        $avg: {
          $map: {
            input: { $ifNull: ["$budgetVar.values", []] },
            as: "val",
            in: { $toDouble: "$$val.value" }
          }
        }
      }
    }
  },
  // Step 6: Final projection to match the desired output structure
  {
    $project: {
      _class: 1,
      name: 1,
      startTimestamp: 1,
      endTimestamp: 1,
      source: 1,
      cities: 1,
      variables: {
        $ifNull: [
          [{
            _id: "$budgetVar._id",
            name: "$budgetVar.name",
            defaultValue: "$budgetVar.defaultValue",
            lastValue: "$budgetVar.lastValue",
            values: [{ value: { $toString: "$avgBudget" } }]
          }],
          []
        ]
      }
    }
  }
])

Let's walk through each stage: #

• $unwind: "$cities": Splits the cities array into separate documents, so we can count each individual city visit.
• First $group: Groups by traveler ID + city ID/name to calculate the visited count. We also retain all core traveler fields and the original variables array for later processing.
• Second $group: Reassembles the traveler's document, pushing the counted city entries back into a formatted cities array.
• $addFields (budgetVar): Filters the variables array to extract only the "Budget" entry—this makes it easier to work with the Budget values.
• $addFields (avgBudget): Converts the Budget's string values to numbers, then calculates the average. We use $ifNull to handle cases where there are no Budget values (avoids errors).
• Final $project: Formats the output to match your requirements. We keep all core traveler fields, the formatted cities array, and rebuild the variables array to only include the Budget entry with its average value. The $ifNull here handles cases where a traveler has no Budget variable at all.

Edge Case Handling: #

• If a traveler has no Budget entries in variables, the pipeline will output an empty variables array.
• If the Budget's values array is empty, the average will be null—you can adjust this with $ifNull to return a default value (like "0") if needed.

内容的提问来源于stack exchange，提问作者Marwan Jaber