Mongo聚合中将数字转换为月份名称的实现求助

Got it, let's get those month names showing up in your aggregation results instead of just numbers! Here are a couple of solid approaches to tweak your existing pipeline:

Approach 1: Use $dateToString (Simplest for Standard Month Names) #

MongoDB's $dateToString operator lets you format dates directly into month names (full or abbreviated). You can either use it in the $group stage to group by month name, or keep grouping by numeric month first (for proper chronological sorting) then convert to a name in the $project stage.

Option 1a: Group by Month Name Directly #

Great if you don't mind sorting by the alphabetical order of month names:

db.collection.aggregate([
  { 
    $match: { 
      CREATE_DATE: { 
        $lte: new Date(), 
        $gte: new Date(new Date().setDate(new Date().getDate() - 120)) 
      } 
    } 
  },
  { 
    $group: { 
      _id: { 
        month: { $dateToString: { format: "%B", date: "$CREATE_DATE" } }, // %B = full month name (e.g., January), %b = abbreviated (e.g., Jan)
        year: { $year: "$CREATE_DATE" }
      },
      avgofozone: { $avg: "$OZONE" }
    } 
  },
  { $sort: { "_id.year": -1, "_id.month": 1 } },
  { 
    $project: {
      _id: 0,
      year: "$_id.year",
      month: "$_id.month",
      average_ozone: "$avgofozone"
    }
  }
])

Option 1b: Group by Numeric Month First (For Chronological Sorting) #

If you want months sorted by their actual order (not alphabetical), group by the numeric month first, then convert to a name in the projection:

db.collection.aggregate([
  { 
    $match: { 
      CREATE_DATE: { 
        $lte: new Date(), 
        $gte: new Date(new Date().setDate(new Date().getDate() - 120)) 
      } 
    } 
  },
  { 
    $group: { 
      _id: { 
        month_num: { $month: "$CREATE_DATE" },
        year: { $year: "$CREATE_DATE" }
      },
      avgofozone: { $avg: "$OZONE" }
    } 
  },
  { $sort: { "_id.year": -1, "_id.month_num": 1 } }, // Sorts months in Jan-Dec order
  { 
    $project: {
      _id: 0,
      year: "$_id.year",
      month: {
        $dateToString: { 
          format: "%B", 
          date: { 
            $dateFromParts: { 
              year: "$_id.year", 
              month: "$_id.month_num", 
              day: 1 
            } 
          }
        }
      },
      average_ozone: "$avgofozone"
    }
  }
])

Approach 2: Use $switch (Custom Month Names) #

If you need non-standard month names (e.g., local language, custom labels), use $switch to map numeric months to your desired names:

db.collection.aggregate([
  { 
    $match: { 
      CREATE_DATE: { 
        $lte: new Date(), 
        $gte: new Date(new Date().setDate(new Date().getDate() - 120)) 
      } 
    } 
  },
  { 
    $group: { 
      _id: { 
        month_num: { $month: "$CREATE_DATE" },
        year: { $year: "$CREATE_DATE" }
      },
      avgofozone: { $avg: "$OZONE" }
    } 
  },
  { $sort: { "_id.year": -1, "_id.month_num": 1 } },
  { 
    $project: {
      _id: 0,
      year: "$_id.year",
      month: {
        $switch: {
          branches: [
            { case: { $eq: ["$_id.month_num", 1] }, then: "January" },
            { case: { $eq: ["$_id.month_num", 2] }, then: "February" },
            { case: { $eq: ["$_id.month_num", 3] }, then: "March" },
            { case: { $eq: ["$_id.month_num", 4] }, then: "April" },
            { case: { $eq: ["$_id.month_num", 5] }, then: "May" },
            { case: { $eq: ["$_id.month_num", 6] }, then: "June" },
            { case: { $eq: ["$_id.month_num", 7] }, then: "July" },
            { case: { $eq: ["$_id.month_num", 8] }, then: "August" },
            { case: { $eq: ["$_id.month_num", 9] }, then: "September" },
            { case: { $eq: ["$_id.month_num", 10] }, then: "October" },
            { case: { $eq: ["$_id.month_num", 11] }, then: "November" },
            { case: { $eq: ["$_id.month_num", 12] }, then: "December" }
          ],
          default: "Unknown"
        }
      },
      average_ozone: "$avgofozone"
    }
  }
])

Quick Notes: #

• %B in $dateToString gives full month names (e.g., "January"), while %b gives abbreviated names (e.g., "Jan").
• The $dateFromParts operator creates a dummy date from the year and numeric month, which we then format to get the name.
• The $switch approach is fully customizable—swap out the then values with your preferred language or labels.

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