CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-frameworkpymongo
gautam
gautam

Reputation: 402

Mongodb groupby on Dictionary inside dictionary

I have a collection which has documents like: Document 1:

{
 "company": "ABC"
 "application": {
    "app-1": {"earning_from_src_A": 50,
              "earning_from_src_B": 43},
    "app-2": {"earning_from_src_A": 10,
              "earning_from_src_B": 13}
 }
}

Document 2:

{
 "company": "ABCD"
 "application": {
    "app-1": {"earning_from_src_A": 150,
              "earning_from_src_B": 413},
    "app-2": {"earning_from_src_A": 110,
              "earning_from_src_B": 113},
    "app-3": {"earning_from_src_A": 1,
              "earning_from_src_B": 34},
 }
}

I would like to get the sum of applications all over the collection documents. like: Results:

{
 "app-1": {"earning_from_src_A": 200,
           "earning_from_src_B": 456},
 "app-2": {"earning_from_src_A": 120,
           "earning_from_src_B": 126},
 "app-3": {"earning_from_src_A": 1,
           "earning_from_src_B": 34},
}

Anyone please help me on this.?

I tried using aggregate function, but i am not able to groupby all over the collection.

Upvotes: 2

Views: 993

Answers (1)

Ashh
Ashh

Reputation: 46491

You can use below aggregation

So basically your keys are unknown here. Therefore, you have to convert them to key value pair using $objectToArray aggregation. Then you can easily $group them using app name and count there earning from A and B. 

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

Which outputs

[
  {
    "app-1": {
      "earning_from_src_A": 200,
      "earning_from_src_B": 456
    },
    "app-2": {
      "earning_from_src_A": 120,
      "earning_from_src_B": 126
    },
    "app-3": {
      "earning_from_src_A": 1,
      "earning_from_src_B": 34
    }
  }
]

Upvotes: 2

Related Questions