Grouping the results of a group query in mongodb

Question

I have the following sample data:

{
    "name": "Bob",
    "mi": "K",
    "martialStatus": "M",
    "age": 30,
    "city": "Paris",
    "job": "Engineer"
}
{
    "name": "Chad",
    "mi": "M",
    "martialStatus": "W",
    "age": 31,
    "city": "Paris",
    "job": "Doctor"
}
{
    "name": "Mel",
    "mi": "A",
    "martialStatus": "D",
    "age": 31,
    "city": "London",
    "job": "Doctor"
}
{
    "name": "Frank",
    "mi": "F",
    "martialStatus": "S",
    "age": 30,
    "city": "London",
    "job": "Engineer"
}

I am trying to write a mongo query that would return results in the following format: "peopleCount": 4, "jobsList": { "job": "Doctor", "ageList": [ { "age": 31, "cityList": [ { "city": "London", "people": [ { "name": "Mel", "martialStatus": "D" } ] }, { "city": "Paris", "people": [ { "name": "Chad", "martialStatus": "W" } ] }, { "city": "Berlin", ... ... ] } ] }

To try on the first two level (jobsList and ageList), I am trying the below

db.colName.aggregate([
    { 
        $group: { 
            _id: { job: "$job" },
            jobsList: {
                $push: {
                    age: "$age",
                    city: "$city",
                    name: "$name",
                    martialStatus: "$martialStatus"
                }
            }
        }
    },
    {
        $group: {
            _id: { age: "$age" }, 
            ageList: {
                $push: {
                    city: "$city", 
                    name: "$name", 
                    martialStatus: "$martialStatus"
                }
            }
        }
    }
]); 

The above however does not work although the first group/push part works... Any hints on how to get that output format/groupping?

Answer 1

db.colName.aggregate([
{
    $group: {
        _id: { job: "$job", age: "$age", city: "$city" },
        people: { $push: { name: "$name", martialStatus: "$martialStatus" } }
    }
},
{
    $group: {
        _id: { job: "$_id.job", age: "$_id.age" },
        peopleCount: { $sum: { $size: "$people" } },
        cityList: { $push: { city: "$_id.city", people: "$people" } },
    }
},
{
    $group: {
        _id: { job: "$_id.job" },
        peopleCount: { $sum: "$peopleCount" },
        agesList: { $push: { age: "$_id.age", cityList: "$cityList" } }
    }
},
{
    $group: {
        _id: null,
        peopleCount: { $sum: "$peopleCount" },
        jobsList: { $push: { job: "$_id.job", agesList: "$agesList" } }
    }
},
{
    $project: { _id: 0, peopleCount: 1, jobsList: 1 }
}
]);

on the provided by you collection gives me the result

{
  "peopleCount" : 4,
  "jobsList" : 
  [ 
    { 
      "job" : "Engineer", 
      "agesList" : 
        [ 
          { 
            "age" : 30, 
            "cityList" : 
              [ 
                { 
                  "city" : "London", 
                  "people" : 
                    [ 
                      { "name" : "Frank", "martialStatus" : "S" } 
                    ] 
                }, 
                { 
                  "city" : "Paris", 
                  "people" : 
                    [ 
                      { "name" : "Bob", "martialStatus" : "M" } 
                    ] 
                } 
              ] 
          } 
        ]
    },
    { 
      "job" : "Doctor", 
      "agesList" : 
        [ 
          { 
            "age" : 31, 
            "cityList" : 
              [ 
                { 
                  "city" : "London", 
                  "people" : 
                    [ 
                      { "name" : "Mel", "martialStatus" : "D" } 
                    ] 
                }, 
                { 
                  "city" : "Paris", 
                  "people" : 
                    [ 
                      { "name" : "Chad", "martialStatus" : "W" } 
                    ] 
                } 
              ] 
          } 
        ] 
    } 
  ] 
}

that seems to be correct. Thought, I am not sure it's the best solution. I am new to aggregation-framework.