MongoDB聚合查询无法获取全部字段，求语法检查及解决方案

Hey Naresh, let's figure out why your MongoDB aggregation isn't returning all fields and fix it up!

The root issue here is the $group stage in your aggregation. By default, MongoDB's $group only preserves the fields you define in the _id object and any aggregation-specific fields you explicitly include (like avgduration in your query). All other original document fields get dropped during this stage—that's why you can't see them in the final output.

Depending on your exact needs, here are a few practical approaches:

1. Manually Include Specific Fields in $group #

If you know exactly which fields you need to keep, you can use operators like $first, $last, $push, or $addToSet to add them to the $group output. For example, if you want to retain CREATE_DATE and RECEIVEDDATE:

db.lights.aggregate([
  { 
    $match: { 
      CREATE_DATE: { 
        $gte: ISODate("2018-01-24T20:05:30.000Z"), 
        $lt: ISODate("2018-02-24T20:05:30.000Z") 
      } 
    } 
  },
  { 
    $addFields: { 
      offduration: { 
        $divide: [{ $subtract: ["$RECEIVEDDATE", "$CREATE_DATE"] }, 3600000] 
      } 
    } 
  },
  { 
    "$group": { 
      _id: { SWITCHID: "$SWITCHID", STATUS: "$STATUS" }, 
      avgduration: { $avg: "$offduration" },
      // Use $first to grab the value from the first document in the group
      CREATE_DATE: { $first: "$CREATE_DATE" },
      RECEIVEDDATE: { $first: "$RECEIVEDDATE" },
      // Add any other fields you need using the same pattern
      YOUR_OTHER_FIELD: { $first: "$YOUR_OTHER_FIELD" }
    } 
  }
])

• Use $first/$last if the field value is consistent across the group.
• Use $push if you want to keep all values from the group, or $addToSet if you want unique values only.

2. Join Back to the Original Collection (For Full Document Fields) #

If you need every field from the original documents, you can first run your grouping logic, then use $lookup to fetch the full documents from the lights collection:

db.lights.aggregate([
  { 
    $match: { 
      CREATE_DATE: { 
        $gte: ISODate("2018-01-24T20:05:30.000Z"), 
        $lt: ISODate("2018-02-24T20:05:30.000Z") 
      } 
    } 
  },
  { 
    $addFields: { 
      offduration: { 
        $divide: [{ $subtract: ["$RECEIVEDDATE", "$CREATE_DATE"] }, 3600000] 
      } 
    } 
  },
  { 
    "$group": { 
      _id: { SWITCHID: "$SWITCHID", STATUS: "$STATUS" }, 
      avgduration: { $avg: "$offduration" },
      // Store the _ids of documents in the group for later lookup
      docIds: { $push: "$_id" }
    } 
  },
  // Join back to the lights collection to get full documents
  {
    $lookup: {
      from: "lights",
      localField: "docIds",
      foreignField: "_id",
      as: "originalDocuments"
    }
  }
])

The originalDocuments array in the output will contain all full documents from the group. You can use $unwind if you want to flatten this array, or keep it as-is depending on your use case.

Quick Performance Tip #

Always keep your $match stage as the first step in the pipeline—it filters down the data early, reducing the amount of data processed in subsequent stages (like $group and $lookup).

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