如何扁平化MongoDB聚合查询的JSON结果？Node.js API求助

Hey there! Let's work through your aggregation pipeline issue. First, let's clean up your original code to make it clearer (since your $project stage was cut off):

app.get('/polute', function (req, res) {
  Light.aggregate([
    { 
      $match: { 
        CREATE_DATE: { 
          $lte: new Date(), 
          $gte: new Date(new Date().setDate(new Date().getDate()-120)) 
        } 
      } 
    },
    { 
      $group: { 
        _id: { 
          month: { $month: "$CREATE_DATE" }, 
          year: { $year: "$CREATE_DATE" } 
        }, 
        avgofozone: { $avg: "$OZONE" } 
      } 
    },
    { $sort: { "year": -1 } },
    { $project: { y... } } // Incomplete stage here
  ])
  .then(result => res.json(result))
  .catch(err => res.status(500).json({ error: err.message }));
});

Looking at this, there are a few key issues that are likely causing unexpected output:

1. Incorrect Sort Target
   Your $sort stage uses "year": -1, but year is nested inside the _id field from the $group stage. MongoDB can't find a top-level year field, so this sort does nothing (or throws an error). You need to target the nested field:

   { $sort: { "_id.year": -1, "_id.month": -1 } }
   

   This will sort results by most recent year first, then most recent month within each year.

2. Incomplete $project Stage
   The cut-off $project stage is either causing a syntax error or not shaping your output correctly. If you want to flatten the nested _id fields and rename the average for readability, try this:

   {
     $project: {
       _id: 0, // Hide the original _id
       year: "$_id.year",
       month: "$_id.month",
       averageOzone: "$avgofozone" // Rename to a more descriptive field
     }
   }
   

3. Potential Date Type Issues
   If your CREATE_DATE field is stored as a string (not a MongoDB Date type), the $month and $year operators won't work correctly. Add an $addFields stage right after $match to convert it to a Date:

   { $addFields: { CREATE_DATE: { $toDate: "$CREATE_DATE" } } }
   

Full Corrected Pipeline #

Here's the updated code with all fixes applied:

app.get('/polute', function (req, res) {
  const fourMonthsAgo = new Date();
  fourMonthsAgo.setDate(fourMonthsAgo.getDate() - 120);

  Light.aggregate([
    // Match documents from the last 120 days
    { 
      $match: { 
        CREATE_DATE: { 
          $lte: new Date(), 
          $gte: fourMonthsAgo 
        } 
      } 
    },
    // Convert CREATE_DATE to Date type (if stored as string)
    { $addFields: { CREATE_DATE: { $toDate: "$CREATE_DATE" } } },
    // Group by year and month, calculate average ozone
    { 
      $group: { 
        _id: { 
          month: { $month: "$CREATE_DATE" }, 
          year: { $year: "$CREATE_DATE" } 
        }, 
        avgofozone: { $avg: "$OZONE" } 
      } 
    },
    // Sort by most recent year/month
    { $sort: { "_id.year": -1, "_id.month": -1 } },
    // Shape the output for readability
    {
      $project: {
        _id: 0,
        year: "$_id.year",
        month: "$_id.month",
        averageOzone: "$avgofozone"
      }
    }
  ])
  .then(result => {
    console.log('Aggregation result:', result);
    res.json(result);
  })
  .catch(err => {
    console.error('Aggregation error:', err);
    res.status(500).json({ error: err.message });
  });
});

Additional Checks to Verify #

• Make sure your OZONE field contains numeric values (not strings or null). If there are non-numeric values, $avg will ignore them or return null.
• Verify that there are documents in your Light collection that fall within the 120-day date range. You can test the $match stage alone to confirm.
• If you're still getting unexpected averages, check for outliers in your OZONE data that might be skewing the result.

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