MongoDB Group By count occurences of values and output as new field

Question

I have a 3 Collections Assignments, Status, Assignee.

Assignments Fields : [_id, status, Assignee]

Assignee and Status Fields : [_id, name].

There can be many assignments associated with various Status and Assignee collections(linked via _id field), There is no nesting or complex data.

I need a query for all assignments ids where Assignees are the row, Status are the Columns, there combined cell is the count with Total counts at the end.

To help you visualize, I am attaching below image. I am new to complex Mongo DB Aggregate framework, kindly guide me to achieve query.

Note: Data in Status and Assignee collection will be dynamic. Nothing is predetermined in the Query. So, the Rows and Columns are going to grow dynamically in future, If the query is given pagination, then it would be of great help. I cannot write a query with hard coded status names like 'pending', 'completed' etc. As data shall grow and existing data may change like 'pending task', 'completed work'.

Below is my query

    db.getCollection('Assignments').aggregate([
    { 
        "$group": {
            "_id": {
                "assignee": "$assignee",
                "statusId": "$statusId"
            },
            "statusCount": { "$sum": 1 }
        }
    },
    { 
        "$group": {
            "_id": "$_id.assignee",
            "statuses": { 
                "$push": { 
                    "statusId": "$_id.statusId",
                    "count": "$statusCount"
                },
            },
            "count": { "$sum": "$statusCount" }
        }
    },
    ]);

Below is the output format:

    {
    "_id" : "John",
    "statuses" : {
        "statusId" : "Pending",
        "count" : 3.0
    },
    "count" : 3.0
}
{
    "_id" : "Katrina",
    "statuses" : [{
        "statusId" : "Pending",
        "count" : 1.0
    },
    {
        "statusId" : "Completed",
        "count" : 1.0
    },
    {
        "statusId" : "Assigned",
        "count" : 1.0
    }],
    "count" : 3.0
}
{
    "_id" : "Collins",
    "statuses" : {
        "statusId" : "Pending",
        "count" : 4.0
    },
    "count" : 4.0
}

Expected Output is:

{
    "_id" : "Katrina",
    "Pending" : 1.0,
    "Completed" : 1.0,
    "Assigned" : 1.0,
    "totalCount" : 3.0
}

Any Idea on how to many various statusId for different assignee as keys and not values in single document.

Answer 1

Why not just keep statuses as an object so each status is a key/val pair. If that works you do the following

db.getCollection('Assignments').aggregate([
    [
     { 
        "$group": {
            "_id": {
                "assignee": "$assignee",
                "statusId": "$statusId"
             },
             "statusCount": { "$sum": 1 }
        },
      },
      { 
        "$group" : { 
            "_id" : "$_id.assignee", 
            "statuses" : { 
                "$push" : { 
                    "k" : "$_id.statusId", // <- "k" as key value important for $arrayToObject Function
                    "v" : "$statusCount" // <- "v" as key value important for $arrayToObject Function
                }
            }, 
            "count" : { 
               "$sum" : "$statusCount"
            }
        }
      }, 
        { 
            "$project" : { 
                "_id" : 1.0, 
                "statuses" : { 
                    "$arrayToObject" : "$statuses"
                }, 
                "totalCount" : "$count"
            }
        }
    ], 
    { 
        "allowDiskUse" : false
    }
);

This gives you:

{
    "_id" : "Katrina",
    "statuses": {
      "Pending" : 1.0,
      "Completed" : 1.0,
      "Assigned" : 1.0,
     },
     "totalCount" : 3.0
}

A compromise having it one layer deeper but still the shape of statuses you wanted and dynamic with each new statusId added.

Answer 2

You need another $group stage after $unwind to count number of status based on statusId string value:

{ 
    "$group": {
        "_id": "$_id",
         "Pending" : {
            "$sum": {
                "$cond": [ 
                    { "$eq": [ 
                        "$statuses.statusId", 
                        "Pending"
                    ]},
                "$statuses.count",
                0 
                ]
            }
        },
        "Completed" : {
            "$sum": {
                "$cond": [ 
                    { "$eq": [ 
                        "$statuses.statusId", 
                        "Completed"
                    ]},
                "$statuses.count",
                0 
                ]
            }
        },
        "Assigned" : {
            "$sum": {
                "$cond": [ 
                    { "$eq": [ 
                        "$statuses.statusId", 
                        "Assigned"
                    ]},
                "$statuses.count",
                0 
                ]
            }
        },
        "totalCount": { "$sum": 1 }
    }
}

The final aggregate command:

db.getCollection('Assignments').aggregate([
    { 
        "$group": {
            "_id": {
                "assignee": "$assignee",
                "statusId": "$statusId"
            },
            "statusCount": { "$sum": 1 }
        }
    },
    { 
        "$group": {
            "_id": "$_id.assignee",
            "statuses": { 
                "$push": { 
                    "statusId": "$_id.statusId",
                    "count": "$statusCount"
                },
            },
            "count": { "$sum": "$statusCount" }
        }
    },
    { "$unwind": "$statuses" },
    { 
        "$group": {
            "_id": "$_id",
             "Pending" : {
                "$sum": {
                    "$cond": [ 
                        { "$eq": [ 
                            "$statuses.statusId", 
                            "Pending"
                        ]},
                    "$statuses.count",
                    0 
                    ]
                }
            },
            "Completed" : {
                "$sum": {
                    "$cond": [ 
                        { "$eq": [ 
                            "$statuses.statusId", 
                            "Completed"
                        ]},
                    "$statuses.count",
                    0 
                    ]
                }
            },
            "Assigned" : {
                "$sum": {
                    "$cond": [ 
                        { "$eq": [ 
                            "$statuses.statusId", 
                            "Assigned"
                        ]},
                    "$statuses.count",
                    0 
                    ]
                }
            },
            "totalCount": { "$sum": 1 }
        }
    }   
]);