Aggregate Key/Value from Dictionaries in PyMongo

user_2955541 0
Viewed 1359

I have a collection of documents that look like:

{
  "id": 45,
  "some_list": [{"x": 1, "y": {"a": 10, "b": 32, "c": 91}}, 
                {"x": 2, "y": {"a": 12, "b": 26, "d": 75, "p": 1}}]
}

or 

{
  "id": 119,
  "some_list": [{"x": 1, "y": {"a": 5}}, 
                {"x": 2, "y": {"b": 60, "p": 8}}]
}

I'd like to retrieve an aggregated result that sums all of the y dictionaries. And get:

{"a": 27, "b": 118, "c": 91, "d": 75, "p": 9}

# a = 10 + 12 + 5
# b = 32 + 26 + 60
# c = 91
# d = 75
# p = 1 + 8

Here's what I've tried:

pipeline = [
    {"$unwind": "$some_list"},
    {"$group": {"_id": "$some_list.y.key", "count": {"$sum": "$some_list.y.value"}}},
]
list(client.db.collection.aggregate(pipeline))
aggregation-framework mongodb-query mongodb
1 Answers

You can use below aggregation

Since you have unknown keys you have to use $objectToArray to convert dynamic keys into k and v format then you can easily $group them.

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

Output

[
  { "_id": "c", "count": 91 },
  { "_id": "d", "count": 75 },
  { "_id": "p", "count": 9 },
  { "_id": "b", "count": 118 },
  { "_id": "a", "count": 27 }
]

And even if you want the data in key value pair then you can use this 

db.collection.aggregate([
  { "$unwind": "$some_list" },
  { "$addFields": {
    "some_list": { "$objectToArray": "$some_list.y" }
  }},
  { "$unwind": "$some_list" },
  { "$group": {
    "_id": "$some_list.k",
    "count": { "$sum": "$some_list.v" }
  }},
  { "$group": {
    "_id": null,
    "data": { "$push": { "k": "$_id", "v": "$count" }}
  }},
  { "$replaceRoot": { "newRoot": { "$arrayToObject": "$data" }}}
])

Output

[
  {
    "a": 27,
    "b": 118,
    "c": 91,
    "d": 75,
    "p": 9
  }
]
user_75106570
Related