如何通过MongoDB聚合调整近4个月空气质量数据的输出格式

Got it, let's adjust your aggregation pipeline to turn the row-per-month results into the wide, column-per-month format you're looking for. Here's the modified pipeline and a breakdown of how it works:

Modified Aggregation Query #

db.Air_pollution.aggregate([
  // 1. Filter records from the last 120 days (same as original)
  {
    $match: {
      CREATE_DATE: {
        $lte: new Date(),
        $gte: new Date(new Date().setDate(new Date().getDate() - 120))
      }
    }
  },
  // 2. Group by year, calculate monthly averages and total average
  {
    $group: {
      _id: { year: { $year: "$CREATE_DATE" } },
      january: { $avg: { $cond: [{ $eq: [{ $month: "$CREATE_DATE" }, 1] }, "$OZONE", null] } },
      february: { $avg: { $cond: [{ $eq: [{ $month: "$CREATE_DATE" }, 2] }, "$OZONE", null] } },
      march: { $avg: { $cond: [{ $eq: [{ $month: "$CREATE_DATE" }, 3] }, "$OZONE", null] } },
      april: { $avg: { $cond: [{ $eq: [{ $month: "$CREATE_DATE" }, 4] }, "$OZONE", null] } },
      total_avg: { $avg: "$OZONE" }
    }
  },
  // 3. Format output to match your desired structure
  {
    $project: {
      _id: 0,
      zone_type: "ozone", // Replace with "$zone_type" if this field exists in your documents
      year: "$_id.year",
      january: { $round: ["$january", 5] },
      february: { $round: ["$february", 5] },
      march: { $round: ["$march", 5] },
      april: { $round: ["$april", 5] },
      avgofozone: "$total_avg"
    }
  },
  // 4. Sort by year descending (same as original)
  {
    $sort: { year: -1 }
  }
])

Key Changes Explained #

1. Row-to-Column Transformation ($group Stage)

   • Instead of grouping by both year and month, we group only by year so all monthly data for a year lands in one row.
   • For each month (January to April, covering your 4-month window), we use $cond to check if the record's month matches. If it does, we include the OZONE value; otherwise, we pass null. $avg automatically ignores null values, so we get the average for that specific month.
   • We also calculate total_avg as the overall average of all OZONE values in the year's 4-month window, which maps to your avgofozone field.

2. Output Formatting ($project Stage)

   • We remove the default _id field for cleaner output.
   • Add the zone_type field: use a fixed value like "ozone" if it's constant, or replace with $zone_type if this field exists in your original documents.
   • Use $round to trim the monthly averages to 5 decimal places, matching the precision in your desired output.
   • Rename total_avg to avgofozone to align with your expected field name.

Handling Dynamic Months #

If your 4-month window shifts (e.g., if you run this in May, you'd need February-May instead of January-April), you can adjust the month numbers in the $cond checks. For example, to target months 2-5, update the january/february/etc. fields to check for 2,3,4,5 instead.

If you want to show all 12 months (with null or 0 for months with no data), just add additional fields for may-december using the same $cond pattern.

内容的提问来源于stack exchange，提问作者Naresh G