如何使用MongoDB聚合获取每个ID的时长？现有语句求优化

Hey there! Let's break down why your current aggregation isn't working as expected and get you the right results.

First: Fix the Basic Syntax Issue #

Looking at your code, you missed wrapping the aggregation stages in an array — MongoDB requires all aggregation stages to be inside []. That's a common syntax mistake that can cause unexpected behavior or errors.

Scenario 1: Add Duration to Each Matching Document #

If you just want to calculate and display the duration (in hours) for every document that falls within your date range, here's the corrected query:

db.lights.aggregate([
  {
    $match: {
      CREATE_DATE: {
        $gte: ISODate("2018-01-24T20:05:30.000Z"),
        $lt: ISODate("2018-02-24T20:02:30.000Z")
      }
    }
  },
  {
    $addFields: {
      duration: {
        $divide: [
          { $subtract: ["$RECEIVEDDATE", "$CREATE_DATE"] },
          3600000 // Convert milliseconds to hours (1hr = 3.6e6 ms)
        ]
      }
    }
  }
])

A quick check to verify: Make sure your field names (CREATE_DATE, RECEIVEDDATE) match exactly with what's in your collection — MongoDB is case-sensitive! If either field is named differently (e.g., receivedDate instead of RECEIVEDDATE), the calculation will return null.

Scenario 2: Calculate Aggregated Duration Per SWITCHID #

If your goal is to get total or average duration grouped by each SWITCHID (which sounds like "each ID's duration" you mentioned), you'll need to add a $group stage to summarize the data. Here's how to do that:

db.lights.aggregate([
  {
    $match: {
      CREATE_DATE: {
        $gte: ISODate("2018-01-24T20:05:30.000Z"),
        $lt: ISODate("2018-02-24T20:02:30.000Z")
      },
      // Optional: Ensure valid date fields to avoid errors
      RECEIVEDDATE: { $exists: true, $type: "date" }
    }
  },
  {
    $addFields: {
      duration: {
        $divide: [
          { $subtract: ["$RECEIVEDDATE", "$CREATE_DATE"] },
          3600000
        ]
      }
    }
  },
  {
    $group: {
      _id: "$SWITCHID", // Group by the SWITCHID
      totalDurationHours: { $sum: "$duration" }, // Total duration for the ID
      averageDurationHours: { $avg: "$duration" }, // Average duration per document (optional)
      // Optional: Keep all documents in the group if you need to review them
      relatedDocuments: { $push: "$$ROOT" }
    }
  }
])

This will output one document per SWITCHID, with aggregated duration metrics and optionally the original documents tied to that ID.

Quick Troubleshooting Tips #

• If you're getting null for duration, double-check that both CREATE_DATE and RECEIVEDDATE are valid ISODate types in your collection.
• If you don't see any results at all, verify your date range — maybe there are no documents falling within that window.

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