mongodb: count and sort content of array-CodePudding

I have a collection loginAttemptList with the following structure:

    {
        "username":  "root",
        "passwords":  [
                          "1234",
                          "root",
                          "123456"
                      ],
    },
    {
            "username":  "admin",
            "passwords":  [
                              "1234",
                              "123456",
                              "admin",
                              "administrator"
                          ],
        },
        {
            "username":  "root",
            "passwords":  [
                              "1234",
                              "root"
                          ],
        }

I am able to count and sort the most used usernames:

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

Now I would like to do the same with passwords. There are 0-10 passwords in an array in every object (given). Is there a possibility to count and sort them as well?

Something like:

"1234" : 3276 (times)

"123456": 2345

"password": 1349

etc.

And further is there a way to list the most used username/password combination?

CodePudding user response：

Simply $unwind the passwords array and group by composite group key.

db.collection.aggregate([
  {
    "$unwind": "$passwords"
  },
  {
    $group: {
      _id: {
        username: "$username",
        password: "$passwords"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $sort: {
      count: -1
    }
  }
])

Here is the Mongo playground for your reference.