MongoDB: Counting an array using group

Question

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

just deconstruct body before $group stage using { $unwind: "$request.body" } — turivishal
– turivishal, Commented Oct 22, 2020 at 15:06

Jits · Accepted Answer · 2020-10-22 15:19:43Z

1

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
                    }
        }
    }]
)

answered Oct 22, 2020 at 15:19

Jits

5453 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ntwanano Over a year ago

Thank you very much kind sir. The solution you provided gives the desired output.

Collectives™ on Stack Overflow

MongoDB: Counting an array using group

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related