MongoDB聚合：通过Unwind与Group统计每日唯一MSISDN数量

Got it, let's break down how to solve this problem using MongoDB's $unwind and $group operations—exactly what you're asking for.

First, let's recap your data structure: each document has a dateHour field (formatted like "2020-02-25 13") and a detail array containing objects with msisdn values. Our goal is to calculate a daily count of unique msisdn entries.

Step-by-Step Aggregation Pipeline #

Here's the full working aggregation query that achieves this:

db.yourCollectionName.aggregate([
  // 1. Unwind the detail array to process each msisdn entry as a separate document
  { $unwind: "$detail" },
  
  // 2. Extract just the date part from dateHour (since it's in "YYYY-MM-DD HH" format)
  {
    $project: {
      date: { $substr: ["$dateHour", 0, 10] }, // Grabs "YYYY-MM-DD" from the string
      msisdn: "$detail.msisdn"
    }
  },
  
  // 3. Group by date, collect only unique msisdn values using $addToSet
  {
    $group: {
      _id: "$date",
      uniqueMsisdns: { $addToSet: "$msisdn" }
    }
  },
  
  // 4. Calculate the count of unique msisdns by getting the size of the set
  {
    $project: {
      date: "$_id",
      uniqueMsisdnCount: { $size: "$uniqueMsisdns" },
      _id: 0 // Hide the default _id field for cleaner output
    }
  }
])

Let's Walk Through Each Stage #

• $unwind: This stage takes the detail array and splits it into individual documents. For example, if a parent document has 3 entries in detail, it becomes 3 separate documents—each with one msisdn value. This is critical because we need to evaluate each msisdn on its own.
• $project: Here we strip the hour component from dateHour to get just the date (using $substr to grab the first 10 characters). We also isolate the msisdn field from the unwound detail object to keep the data focused.
• $group: We group all documents by the extracted date. Using $addToSet ensures that only unique msisdn values are added to the uniqueMsisdns array—duplicates are automatically filtered out.
• $project: Finally, we rename _id to date for readability, use $size to count how many unique msisdns are in the set, and hide the original _id field.

Quick Note on Timestamp Values #

I spotted a typo in one of your timestamp entries ("2020-02-2025")—if your timestamps are correctly formatted, you could also extract the date from them using $dateFromString and $dateToString. However, using dateHour is more efficient since it's already pre-formatted to the hour.

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