MongoDB aggregate with group by nested field and count by another field

Question

I've three different documents as follows

{
  "category" : "aaaaa",
  "summary" : {
    "details" : {
      "city" : "abc"
      "year_of_reg" : "2012",
      "dept" : "dev"
    }
  }
}

{
  "category" : "bbbb",
  "summary" : {
    "details" : {
      "city" : "abc",
      "year_of_reg" : "2016",
      "dept" : "dev"
    }
  }
}

{
  "category" : "aaaaa",
  "summary" : {
    "details" : {
      "dept" : "ui",
      "year_of_reg" : "2018"
    }
  }
}

I want to group the results based on the keys available in details under summary and count based on category. The final result should be like as below

{
  "dep_dev":[
      {
         "category":"aaaaa",
         "count":1.0
      },
      {
         "category":"bbbb",
         "count":1.0
      }
   ],
   "dep_ui":[
      {
         "category":"aaaaa",
         "count":1.0
      }
   ],
   "year_of_reg_2012":[
      {
         "category":"aaaaa",
         "count":1.0
      }
   ],
   "year_of_reg_2016":[
      {
         "category":"bbbb",
         "count":1.0
      }
   ],
   "year_of_reg_2018":[
      {
         "category":"aaaaa",
         "count":1.0
      }
   ],      
   "city_abc":[
      {
         "category":"aaaaa",
         "count":1.0
      },
      {
         "category":"bbbb",
         "count":1.0
      }
   ]
}

How this can be achieved in mongo aggregate? Can this be done using facet? How output keys can be generated dynamically in aggregate? Is there any possible way to get all the keys available under details using a single mongo query?

Answer 1

You would need to run the following aggregate pipeline to get the desired results:

db.getCollection('test').aggregate([
    /*
    1. Create a field with an array of the summary details key concatenated with their 
       corresponding values. 
    */
    { "$addFields": { 
        "summary": {
            "$map": {
                "input": { "$objectToArray": "$summary.details" },
                "as": "el",
                "in": {
                    "$concat": ["$$el.k", "_", "$$el.v"]
                }
            }
        }
    } },

    /*
    2. Flatten the new array to produce a copy of each document per array entry. 
    */
    { "$unwind": "$summary" },

    /*
    3. Group the documents initially by the key and category. 
    */
    { "$group": {
        "_id": { 
            "key": "$summary",
            "category": "$category"
        },
        "count": { "$sum": 1 }
    } },

    /*
    4. Group the input documents from the previous pipeline by the key and aggregate the 
       category and corresponding counts  
    */
    {  "$group": {
        "_id": "$_id.key",
        "counts": {
            "$push": {
                "category": "$_id.category",
                "count": "$count"
            }
        }
    } },

    /*
    4. Calculate accumulated values for all the input documents as a whole.
    */
    {  "$group": {
        "_id": null,
        "counts": {
            "$push": {
                "k": "$_id",
                "v": "$counts"
            }
        }
    } },
    { "$replaceRoot": {
        "newRoot": { "$arrayToObject": "$counts" }
    } } 
])