如何使用MongoDB Aggregate多字段分组实现用户维度去重后的Key统计？

The problem with your initial query is that it counts every document, including duplicates where the same user has the same key multiple times. To get accurate counts of unique users per key, we need to first eliminate these duplicates by grouping on both user_id and key, then aggregate the results by key.

Here's the complete aggregate pipeline that achieves this:

db.test.aggregate([
  // Step 1: Create unique user-key pairs
  {
    $group: {
      _id: {
        user_id: "$user_id",
        key: "$key"
      }
    }
  },
  // Step 2: Count unique users per key
  {
    $group: {
      _id: "$_id.key",
      count: { $sum: 1 }
    }
  },
  // Optional: Sort results by count descending
  {
    $sort: { count: -1 }
  },
  // Optional: Format output to match your expected structure
  {
    $project: {
      _id: 0,
      key: "$_id",
      count: 1
    }
  }
])

Let's break down each stage: #

• First $group stage: This groups documents by both user_id and key, creating one entry for every unique combination. We don't need a count here—we just need to ensure each user-key pair is represented once.
• Second $group stage: Now we take those unique pairs and group by key, summing 1 for each pair to get the total number of distinct users associated with that key.
• $sort stage: Optional, but included to match your original query's descending count order.
• $project stage: Optional, but renames the _id field to key to produce the clean output format you specified.

When run against your sample data, this pipeline will return exactly the expected results:

{"key": "key1", "count": 3}, {"key": "key2", "count": 2}

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