After 3 collections was joined and many many groupings later i have documents like:

{
"Request" : "first",
"TotalRequests" : 12,
"Organization" : "A",
"TotalRequestsByOrganization" : 3,
"user" : "John Smith",
"TotalRequestsByUserInOrganization" : 1
},

{
"Request" : "first",
"TotalRequests" : 12,
"Organization" : "A",
"TotalRequestsByOrganization" : 3,
"user" : "John Galt",
"TotalRequestsByUserInOrganization" : 2
},

{
"Request" : "first",
"TotalRequests" : 12,
"Organization" : "B",
"TotalRequestsByOrganization" : 7,
"user" : "Chris Evans",
"TotalRequestsByUserInOrganization" : 4
},

{
"Request" : "first",
"TotalRequests" : 12,
"Organization" : "B",
"TotalRequestsByOrganization" : 7,
"user" : "Charlie",
"TotalRequestsByUserInOrganization" : 3
},

{
"Request" : "second",
"TotalRequests" : 3,
"Organization" : "B",
"TotalRequestsByOrganization" : 3,
"user" : "James Anthony",
"TotalRequestsByUserInOrganization" : 3
}

etc.

How to group my documents, when "user" and "TotalRequestsByUserInOrganization" is nested into "Organization" and "TotalRequestsByOrganization", and they are part of "Request" and "TotalRequests"?

What i want in result:

{
"Request" : {
"name" : "first",
"TotalRequests" : 12,
"Organization": {
          "Orgname": "A",
          "TotalRequestsByOrganization": 3,
          "users": {
                    "user" : "John Smith",
                    "TotalRequestsByUserInOrganization" : 1,
                    "user" : "John Galt",
                    "TotalRequestsByUserInOrganization" : 2
}}}}

CodePudding user response：

You can achieve the expected result by 2 $group:

1. $group by finest level: Request, TotalRequests, Organization, TotalRequestsByOrganization; use $push to put all user and TotalRequestsByUserInOrganization tuple into an array user
2. $group by coarser level: Request, TotalRequests; use $push to put all Orgname, TotalRequestsByOrganization and users array into an array Organization
3. At the most coarse level, $project the field to wrangle the output to your expected format

Here is the Mongo playground for your reference.