Aggregate Field Values to Separate Key Names

Question

I have a collection in MongoDB with sample data something like this (simplified):

{
    _id: 1,
    username: "ted",
    content: "4125151",
    status: "complete"
}
{
    _id: 2,
    username: "sam",
    content: "4151",
    status: "new"
}
{
    _id: 3,
    username: "ted",
    content: "511",
    status: "new"
}
{
    _id: 4,
    username: "ted",
    content: "411",
    status: "in_progress"
}
{
    _id: 5,
    username: "pat",
    content: "1sds51",
    status: "complete"
}
{
    _id: 6,
    username: "ted",
    content: "4151",
    status: "in_progress"
}
{
    _id: 7,
    username: "ted",
    content: "4125",
    status: "in_progress"
}

I need to aggregate the data such that for each user, I get a count for each status value as well as a total number of records. The result should look like this:

[
{
    username: “pat”,
    new: 0,
    in_progress: 0,
    complete: 1,
    total: 1
},

{
    username: “sam”,
    new: 1,
    in_progress: 0,
    complete: 0,
    total: 1
},

{
    username: “ted”,
    new: 1,
    in_progress: 3,
    complete: 1,
    total: 5
}
]

Or any format that will effectively serve the same purpose which is, I want to be able to use with ngRepeat to display on the front end in this format:

User    New     In Progress Complete    Total
pat     0       0           1           1
sam     1       0           0           1
ted     1       3           1           5

I can perform this aggregation:

{
    "$group": {
        "_id": {
            "username": "$username",
            "status": "$status"
        },
        "count": {
            "$sum": 1
        }
    }
}

This gives me the individual count for each user/status combination that has at least one record. But then I have to piece it together to get it in the format that I can use on the front end. This is not at all ideal.

Is there a way to perform the aggregation to get the data in the format that I need?

Answer 1

Another answer using $group twice and a $push, In this below query you need to compute the final total on UI side.

    db.collection.aggregate([
  {
    "$group": {
      "_id": {
        "username": "$username",
        "status": "$status"
      },
      "statuscount": {
        "$sum": 1
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.username",
      "finalstatus": {
        "$push": {
          "Status": "$_id.status",
          "statuscount": "$statuscount"
        }
      }
    }
  }
])

Answer 2

What you want is a "conditional" aggregation of the values to produce a distinct field property for each status.

This is pretty simple to do using the $cond operator:

[
    { "$group": {
        "_id": "$username",
        "new": { "$sum": { "$cond": [{ "$eq": [ "$status", "new" ] },1,0 ] } },
        "complete": { "$sum": { "$cond": [{ "$eq": [ "$status", "complete" ] },1,0 ] } },
        "in_progress": { "$sum": { "$cond": [{ "$eq": [ "$status", "in_progress" ] },1,0 ] } },
        "total": { "$sum": 1 }
    }}
]

Presuming of course those are the only "status" values, but if they are not then just add an additional $project to sum the fields you want:

[
    { "$match": { "status": { "$in": [ "new", "complete", "in_progress" ] } } },
    { "$group": {
        "_id": "$username",
        "new": { "$sum": { "$cond": [{ "$eq": [ "$status", "new" ] },1,0 ] } },
        "complete": { "$sum": { "$cond": [{ "$eq": [ "$status", "complete" ] },1,0 ] } },
        "in_progress": { "$sum": { "$cond": [{ "$eq": [ "$status", "in_progress" ] },1,0 ] } }
    }},
    { "$project": {
        "new": 1,
        "complete": 1,
        "in_progress": 1,
        "total": { "$add": [ "$new", "$complete", "$in_progress" ] }
]

Or just include that $add within the $group with the same calculations for the separate fields. But the $match is probably just the best idea if there are indeed other status values you don't want.