MongoDB复杂查询需求：统计活跃产品组内产品总数

Got it, let's work through this MongoDB query problem together. From what you've described, we need to count all products that live under active product groups (where status=1), and those products are stored in the data field. I'll cover two common scenarios based on your group hierarchy example since nested structures can change the approach a bit.

Scenario 1: Products are directly stored in the top-level data array #

If each active product group document's data field is a flat array containing all its products (regardless of internal sub-grouping), you can use an aggregation pipeline to get the counts you need:

db.productGroups.aggregate([
  // First, filter only active product groups to reduce processing
  { $match: { status: 1 } },
  // Add a field to calculate how many products are in each group's data array
  { $addFields: { productCount: { $size: "$data" } } },
  // Optional: If you want the total number of products across all active groups
  { $group: { _id: null, totalProducts: { $sum: "$productCount" } } }
])

Breakdown: #

• The $match stage narrows down our dataset to only active groups first, which makes the rest of the pipeline more efficient.
• $addFields creates a productCount field for each group, using $size to get the length of the data array (aka the number of products in that group).
• If you want individual product counts per active group, just remove the final $group stage. If you want the grand total across all active groups, keep it to sum up all the group-level counts.

Scenario 2: Product groups have nested sub-groups with their own data arrays #

Based on your hierarchy example (ProductGroup → Sub-Group → Products), if your documents have nested sub-groups where each sub-group's data field holds its products (like the sample structure below), we'll need to flatten the nested arrays first:

Sample nested document structure:

{
  "_id": ObjectId("..."),
  "status": 1,
  "name": "ProductGroup1",
  "data": [
    {
      "name": "Group1",
      "data": ["product11", "product12"]
    },
    {
      "name": "Group2",
      "data": ["product21"]
    }
  ]
}

Here's the aggregation query to count products in this case:

db.productGroups.aggregate([
  // Filter active groups first
  { $match: { status: 1 } },
  // Unwind the top-level data array to get individual sub-groups
  { $unwind: "$data" },
  // Unwind the sub-group's data array to get individual products
  { $unwind: "$data.data" },
  // Count total products across all active groups
  { $group: { _id: null, totalProducts: { $sum: 1 } } }
])

If you want to count products per top-level product group instead of a grand total, adjust the $group stage like this:

db.productGroups.aggregate([
  { $match: { status: 1 } },
  { $unwind: "$data" },
  { $unwind: "$data.data" },
  { $group: { 
      _id: "$name", // Group by the top-level product group name
      productCount: { $sum: 1 } 
    } 
  }
])

Breakdown: #

• $unwind splits arrays into individual documents: first we split the top-level sub-groups, then split each sub-group's product array so every product becomes its own document.
• From there, $group lets us tally up the counts either across all groups or per top-level group.

Quick Tips: #

• If your data fields might be empty or non-arrays, use $ifNull to avoid errors. For example, replace $size: "$data" with $size: { $ifNull: ["$data", []] }.
• This logic works even if products are objects (like { "productId": "123", "name": "Product X" }) instead of strings—$size and $unwind handle object arrays the same way.

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