CODEWITHSUNDEEP
mongodb
Ntwanano
Ntwanano

Reputation: 33

MongoDB: Counting an array using group

I need a way to group and count an array. What I want to achieve is return count by "type"

The below is how my Department document looks like

{
    "_id" : ObjectId("1234566789"),
    "jobStatus" : "OK",
    "request" : 
     {
        "header" : {
                "timestamp" : "2020-10-21 00:00:00.000Z",
                "action" : "importAction",
                "type" : "registration"
            },
        "body" : [{
                "system_header" : {
                    "student" : "2019051",
                    "course" : "Business"
                }
              }]
     }
}

When counting on the I did the first search with the below

db.department.aggregate(
    [{
        $match: {
            sysDateI: {
                $gt: ISODate("2020-10-18 22:00:00.000Z"),
                $lt: ISODate("2020-10-19 22:00:00.000Z")
            }
        }
    }, {
    $group: {
        _id: '$request.header.type',
        COUNT: {
            $sum: 1
                    }
        }
    }]
)

and produced the following which was satisfactory

/* 1 */
{
    "_id" : "alumni",
    "COUNT" : 3
}

/* 2 */
{
    "_id" : "graduation",
    "COUNT" : 5
}

/* 3 */
{
    "_id" : "registration",
    "COUNT" : 43
}

But when I try to count the contents from the "body" section using the below it return the contents of the array instead of summing it

The code

db.department.aggregate(
    [{
        $match: {
            sysDateI: {
                $gt: ISODate("2020-10-18 22:00:00.000Z"),
                $lt: ISODate("2020-10-19 22:00:00.000Z")
            }
        }
    }, {
    $group: {
        _id: '$request.body.system_header.course',
        COUNT: {
            $sum: 1
                    }
        }
    }]
)

Results are as follows

{
    "_id":[ 
    "Education", 
    "Education", 
    "Education", 
    "Business", 
    "Science"
    ],
    "COUNT" : 1.0
}

What I need returned is something like this. I do realize that system_header is an array.

{
    "_id":"Business",
    "COUNT":45
}

{
    "_id":"Education",
    "COUNT":3
}

Your guidance will be appreciated

Upvotes: 1

Views: 33

Answers (1)

Jits
Jits

Reputation: 545

Hi add $unwind before group like this :

db.department.aggregate(
    [{
        $match: {
            sysDateI: {
                $gt: ISODate("2020-10-18 22:00:00.000Z"),
                $lt: ISODate("2020-10-19 22:00:00.000Z")
            }
        }
    }, 
    {
             $unwind:  "$request.body"
     },
    {
    $group: {
        _id: '$request.body.system_header.course',
        COUNT: {
            $sum: 1
                    }
        }
    }]
)

Upvotes: 1

Related Questions