How to group by multiple columns and multiple values in mongodb

Question

Mongodb Collection -

[
{ name: 'name 1', call: 'Success Call' },
{ name: 'name 1', call: 'Repeat Call' },
{ name: 'name 3', call: 'Repeat Call' },
{ name: 'name 3', call: 'Unsuccess Call' }
{ name: 'name 2', call: 'Success Call' },
{ name: 'name 2', call: 'Repeat Call' }
{ name: 'name 2', call: 'Repeat Call' }
{ name: 'name 2', call: 'Unsuccess Call' }
.
.
.
.
]

i followed this link group and sum multiple columns using aggregation framework and ended up with this output.

[
{
    "_id": 1,
    "Calls": [
        {
            "call": "Repeat Call",
            "callCount": 2
        },
        {
            "call": "Unsuccess Call",
            "callCount": 1
        },
        {
            "call": "Success Call",
            "callCount": 1
        }
    ],
    "Names": [
        {
            "name": {
                "name": "name 4"
            },
            "nameCount": 1
        },
        {
            "name": {
                "name": "name 3"
            },
            "nameCount": 1
        },
        {
            "name": {
                "name": "name 1"
            },
            "nameCount": 2
        }
    ]
}
]

Whereas i actually need something like this

[
{
    "name": "name 4",
    "nameCount": 1,
    "Success Call":1,
    "Unsuccess Call":0,
    "Repeat Call":0
},
{
    "name": "name 3",
    "nameCount": 1,
    "Success Call":2,
    "Unsuccess Call":3,
    "Repeat Call":1
},
{
    "name": "name 1",
    "nameCount": 2,
    "Success Call":1,
    "Unsuccess Call":1,
    "Repeat Call":1
}
]

Can anyone help me sorting this out ? Thanks in advance!

Answer 1

Within the aggregation pipeline, for MongoDB version 3.6 and newer, you can leverage the use of $arrayToObject operator and a $replaceRoot pipeline to get the desired JSON output. This works well for unknown values of the call field.

You would need to run the following aggregate pipeline:

db.collection.aggregate([
    { "$group": {
        "_id": { 
            "name": "$name",
            "call": "$call"
        },
        "count": { "$sum": 1 }
    } },
    { "$group": {
        "_id": "$_id.name",
        "counts": {
            "$push": {
                "k": "$_id.call",
                "v": "$count"
            }
        },
        "nameCount": { "$sum": 1 }
    } },
    { "$replaceRoot": { 
        "newRoot": { 
            "$mergeObjects": [ 
                { "$arrayToObject": "$counts" }, 
                "$$ROOT" 
             ] 
        } 
    } },
    { "$project": { "counts": 0 } }
])

---

For earlier MongoDB versions which do not have support for the above operators, take advantage of the $cond operator in the $group stage to evaluate the counts based on the known call values, something like the following:

db.collection.aggregate([
    { "$group": {
        "_id": "$name",
        "nameCount": { "$sum": 1 },
        "Success Call": {
            "$sum": {
                "$cond": [ { "$eq": [ "$call",  "Success Call" ] }, 1, 0]
            }
        },
        "Repeat Call": {
            "$sum": {
                "$cond": [ { "$eq": [ "$call",  "Repeat Call" ] }, 1, 0]
            }
        },
        "Unsuccess Call": {
            "$sum": {
                "$cond": [ { "$eq": [ "$call",  "Unsuccess Call" ] }, 1, 0]
            }
        }
    } },
    { "$project": {
        "_id": 0, 
        "name": "$_id",
        "nameCount": 1,
        "Success Call":1,
        "Unsuccess Call":1,
        "Repeat Call":1               
    } }
])