如何在MongoDB中获取指定客户端开启设备的总功率及分项总和？

Solution #

Got it, let's craft the MongoDB aggregation pipeline that delivers exactly what you need. First, let's assume your documents look something like this (adjust if your structure varies slightly):

{
  "client": "ashwini",
  "name": "Living Room",
  "location": "Apartment 123",
  "light": { "status": "on", "power": 40 },
  "ac": { "status": "on", "power": 250 },
  "fan": { "status": "off", "power": 35 }
}

Here's the aggregation pipeline that computes both the total on-power for all devices and the split sums grouped by name and location:

db.yourCollectionName.aggregate([
  // Step 1: Filter only documents for client "ashwini"
  { $match: { client: "ashwini" } },
  
  // Step 2: Use $facet to compute two separate results in one pipeline
  {
    $facet: {
      // Part 1: Total sum of all on devices' power
      total_on_power: [
        {
          $project: {
            // Calculate sum of on-device power per document
            doc_total: {
              $sum: [
                { $cond: [{ $eq: ["$light.status", "on"] }, "$light.power", 0] },
                { $cond: [{ $eq: ["$ac.status", "on"] }, "$ac.power", 0] },
                { $cond: [{ $eq: ["$fan.status", "on"] }, "$fan.power", 0] }
              ]
            }
          }
        },
        { $group: { _id: null, total: { $sum: "$doc_total" } } },
        { $project: { _id: 0, value: "$total" } }
      ],
      
      // Part 2: Split sums grouped by name and location
      split_by_location_name: [
        {
          $group: {
            _id: { name: "$name", location: "$location" },
            // Sum each device's on-power for the group
            light_sum: {
              $sum: { $cond: [{ $eq: ["$light.status", "on"] }, "$light.power", 0] }
            },
            ac_sum: {
              $sum: { $cond: [{ $eq: ["$ac.status", "on"] }, "$ac.power", 0] }
            },
            fan_sum: {
              $sum: { $cond: [{ $eq: ["$fan.status", "on"] }, "$fan.power", 0] }
            }
          }
        },
        // Clean up the output structure
        {
          $project: {
            _id: 0,
            name: "$_id.name",
            location: "$_id.location",
            light_power: "$light_sum",
            ac_power: "$ac_sum",
            fan_power: "$fan_sum"
          }
        }
      ]
    }
  },
  
  // Step 3: Finalize the output structure to match your expectation
  {
    $project: {
      total_on_power: { $arrayElemAt: ["$total_on_power.value", 0] },
      split: "$split_by_location_name"
    }
  }
])

Explanation of Key Stages: #

• $match: Narrows down the documents to only those belonging to client "ashwini"—this keeps the pipeline efficient by avoiding unnecessary processing.
• $facet: Lets us run two separate aggregation workflows in one go:
  • total_on_power: First calculates the sum of on-device power per document, then aggregates those sums to get the grand total.
  • split_by_location_name: Groups documents by the unique name + location pair, then sums the power for each device type only if it's in "on" status.
• Final $project: Reshapes the output to present the total as a single value and the split results as an array of objects, which aligns with your requirements.

Sample Output: #

{
  "total_on_power": 580,
  "split": [
    {
      "name": "Living Room",
      "location": "Apartment 123",
      "light_power": 80,
      "ac_power": 500,
      "fan_power": 0
    },
    {
      "name": "Bedroom",
      "location": "Apartment 123",
      "light_power": 0,
      "ac_power": 0,
      "fan_power": 0
    }
  ]
}

If your document structure has variations (like devices stored in an array instead of top-level fields), let me know and I can adjust the pipeline accordingly!

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