MongoDB aggregate group by multiple fields

user_9328497 0
Viewed 1420

Example documents

{
    "user_id": "1",
    "key": "key1"
},
{
    "user_id": "1",
    "key": "key2"
},
{
    "user_id": "2",
    "key": "key1"
},
{
    "user_id": "2",
    "key": "key1"
},
{
    "user_id": "3",
    "key": "key2"
},
{
    "user_id": "4",
    "key": "key1"
}

If I want to group by only the key field, I can use the query below.

db.test.aggregate([
    {
        $group: {
            _id: {
                "key": $key"
            },
            count: {
                $sum: 1
            }
        }
    },
    {
        $sort: {"count":-1}
    },
])

It will group by key and sort with count of key.

However, as you can see from the above documents, there are cases where a single user has a duplicate key.

Therefore, I have to group by with user_id field as the target.

Expected data:

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

How can I group by with multiple fields?

Thanks.

aggregation-framework pymongo mongodb
1 Answers
  • $group by key and construct the array os unique user_id by $addToSet operator
  • $size to get total elements in user_id
db.test.aggregate([
  {
    $group: {
      _id: "$key",
      count: { $addToSet: "$user_id" }
    }
  },
  { $addFields: { count: { $size: "$count" } } },
  { $sort: { count: -1 } }
])

Playground

The second possible way to understand the $group stage,

  • $group by both fields key and user_id
  • $group by the only key and get a sum of the count
db.test.aggregate([
  {
    $group: {
      _id: {
        key: "$key",
        user_id: "$user_id"
      }
    }
  },
  {
    $group: {
      _id: "$_id.key",
      count: { $sum: 1 }
    }
  },
  { $sort: { count: -1 } }
])

Playground

user_89871280
Related