MongoDB 3.2按周期统计嵌套数组中产品总数的聚合查询需求

Let’s work through this step by step—since you’re stuck on MongoDB 3.2 and can’t restructure your 6M+ documents, we’ll use aggregation operators supported in that version to get the product count per period.

First, let’s recap your document structure for clarity:

{ 
  "period": ISODate("2018-05-29T22:00:00.000+0000"), 
  "totalHits": 13982, 
  "hits": [ 
    { 
      // other hit fields...
      users: [ 
        { 
          // other user fields...
          userId: 1, 
          products: [ 
            { productId: 1, price: 30 }, 
            { productId: 2, price: 30 }, 
            { productId: 3, price: 30 }, 
            { productId: 4, price: 30 }
          ] 
        }
      ] 
    } 
  ] 
}

Aggregation Pipeline #

Here’s the pipeline that will calculate the total product count per period:

db.yourCollection.aggregate([
  // Step 1: Unwind the top-level hits array to access nested users
  { $unwind: "$hits" },
  // Step 2: Unwind the users array inside each hit to calculate per-user product counts
  { $unwind: "$hits.users" },
  // Step 3: Calculate how many products each user has (avoids unwinding products array)
  {
    $project: {
      period: 1,
      productCount: { $size: "$hits.users.products" }
    }
  },
  // Step 4: Group by period and sum all product counts for the period
  {
    $group: {
      _id: "$period",
      count: { $sum: "$productCount" }
    }
  },
  // Step 5: Reshape output to match your desired format
  {
    $project: {
      _id: 0,
      period: "$_id",
      count: 1
    }
  },
  // Optional: Sort results chronologically
  { $sort: { period: 1 } }
])

How This Works #

• $unwind "$hits": Flattens the top-level hits array, turning each hit into its own document so we can access the nested users array.
• $unwind "$hits.users": Flattens the users array inside each hit, letting us calculate product counts for individual users.
• $project with $size: Uses $size to get the number of products per user—this is way more efficient than unwinding the products array itself (which would generate millions of extra documents and slow down your pipeline).
• $group by period: Sums all the per-user product counts to get the total number of products in each period.
• Final $project: Renames _id to period and removes the old _id field to match your desired output structure.

Expected Output #

Running this pipeline will return results like:

[ 
  { "period": ISODate("2018-05-27T22:00:00.000+0000"), "count": 432 }, 
  { "period": ISODate("2018-05-28T22:00:00.000+0000"), "count": 442 }, 
  { "period": ISODate("2018-05-29T22:00:00.000+0000"), "count": 519 } 
]

内容的提问来源于stack exchange，提问作者Robert W. Hunter