How to aggregate through nested dictionaries, sum its values, and rank them accordingly?

user_13678418 0
Viewed 430

I have a MongoDB database containing frequencies of words in the document level as shown below. I have about 175k documents in the same format, totaling about 2.5GB.

{
    "_id": xxx,
    "title": "zzz",
    "vectors": {
        "word1": 28,
        "word2": 22,
        "word3": 12,
        "word4": 7,
        "word5": 4
    }

Now I want to iterate through all documents, calculate the sum of all frequencies for each word, and get a total ranking of these words I have in the vectors field based on the frequencies as such:

{
    "vectors": {
        "word1": 223458,
        "word2": 98562,
        "word3": 76433,
        "word4": 4570,
        "word5": 2599
    }

$unwind does not seem to work here as I have a nested dictionary. I'm relatively new to MongoDB, and I couldn't find answers specific to this. Any ideas?

aggregation-framework pymongo mongodb python
1 Answers

You have to convert the keys of sub-object to value using $objectToArray and then $unwind the newly converted array ($unwind will work only for array fields, that's why it didn't work for you).

Finally, group by $vectors.k where the sub-object key has been converted to value.

db.collection.aggregate([
  {
    "$project": {
      "vectors": {
        "$objectToArray": "$vectors"
      }
    },
  },
  {
    "$unwind": "$vectors"
  },
  {
    "$group": {
      "_id": "$vectors.k",
      "count": {
        "$sum": "$vectors.v"
      },
    },
  },
])

Mongo Playground Sample Execution

user_82132770
Related