MongoDB Select Count And Group By

Mongodb Count Group Select Featured Image

In this tutorial, we will discuss the MongoDB select, count, and group by aggregate stages.

The aggregate count and group stage together return the number of documents in a group. We will see an example for this topic in this guide so you can get a better idea of how to select count and group by.

The MongoDB Count & Group Syntax

Let us take a look at the count and group aggregate stages syntax in MongoDB:

db.drones.aggregate([
 {"$group":{fieldName, countFieldName:{$sum:1}}}])

Using the Aggregate Count & Group Stages in MongoDB

Let us start with the examples to cover in this guide for count and group aggregate stages. Below is the collection I will be using:

> db.drones.find().pretty()
{
        "_id" : ObjectId("61673f46b34f185eb7b2bf0c"),
        "onSale" : false,
        "name" : "Nimbari Gryphon Medeta 65",
        "price" : 77500,
        "weight" : "77 kilograms",
        "additionalDetails" : {
                "material" : "carbon fiber",
                "moreUses" : [
                        "Precision Agriculture",
                        "Land Inspection",
                        "Water Inspection",
                        "Cinematography"
                ]
        },
        "utility" : [
                "Recreation",
                "Photography",
                "Videography",
                "Delivery",
                "Natural Resource Exploration"
        ]
}
{
        "_id" : ObjectId("61673f46b34f185eb7b2bf0d"),
        "onSale" : false,
        "name" : "X-Strimmer Eye",
        "price" : 23500,
        "weight" : "24 kilograms",
        "additionalDetails" : {
                "material" : "glass fiber",
                "moreUses" : [
                        "Precision Agriculture",
                        "Cinematography"
                ]
        },
        "utility" : [
                "Recreation",
                "Photography",
                "Videography",
                "Delivery",
                "Natural Resource Exploration"
        ]
}
{
        "_id" : ObjectId("61673f46b34f185eb7b2bf0e"),
        "onSale" : false,
        "name" : "Khai Balemosh Shefqa TRX",
        "price" : 120500,
        "weight" : "80 kilograms",
        "additionalDetails" : {
                "material" : "aluminum",
                "moreUses" : [
                        "Precision Agriculture",
                        "Land Inspection"
                ]
        },
        "utility" : [
                "Recreation",
                "Photography",
                "Videography",
                "Delivery",
                "Natural Resource Exploration"
        ]
}
{
        "_id" : ObjectId("61673f46b34f185eb7b2bf0f"),
        "onSale" : false,
        "name" : "Sifinist Croma AX",
        "price" : 99500,
        "weight" : "97 kilograms",
        "additionalDetails" : {
                "material" : "lithium",
                "moreUses" : [
                        "Precision Agriculture",
                        "Land Inspection",
                        "Water Inspection",
                        "Videography"
                ]
        },
        "utility" : [
                "Recreation",
                "Photography",
                "Videography",
                "Delivery",
                "Natural Resource Exploration"
        ]
}
{
        "_id" : ObjectId("61673f46b34f185eb7b2bf10"),
        "onSale" : false,
        "name" : "Drovce Finnifield FR-7",
        "price" : 87600,
        "weight" : "13 kilograms",
        "additionalDetails" : {
                "material" : "polysterene",
                "moreUses" : [
                        "Precision Agriculture",
                        "Land Inspection",
                        "Water Inspection",
                        "Videography"
                ]
        },
        "utility" : [
                "Recreation",
                "Photography",
                "Videography",
                "Delivery",
                "Natural Resource Exploration"
        ]
}
{
        "_id" : ObjectId("6195514a50f8bacf51bdb6ac"),
        "onSale" : true,
        "name" : "PlayGarra 2078-56",
        "price" : 15000,
        "weight" : "5 kilograms",
        "additionalDetails" : {
                "material" : "polysterene",
                "moreUses" : [
                        "Cinematography",
                        "Wildlife Watching"
                ]
        },
        "utility" : [
                "Recreation",
                "Photography",
                "Videography",
                "Delivery",
                "Natural Resource Exploration"
        ]
}
{
        "_id" : ObjectId("6195514a50f8bacf51bdb6ad"),
        "onSale" : true,
        "name" : "Frinty Gemini 3X70",
        "price" : 55000,
        "weight" : "22 kilograms",
        "additionalDetails" : {
                "material" : "aluminum",
                "moreUses" : [
                        "Cinematography",
                        "Wildlife Watching",
                        "Precision Agriculture",
                        "Land Inspection",
                        "Water Inspection",
                        "Videography"
                ]
        },
        "utility" : [
                "Recreation",
                "Photography",
                "Videography",
                "Delivery",
                "Natural Resource Exploration"
        ]
}
{
        "_id" : ObjectId("6195514a50f8bacf51bdb6ae"),
        "onSale" : true,
        "name" : "Rilche Gabbana Flier RG950",
        "price" : 110000,
        "weight" : "100 kilograms",
        "additionalDetails" : {
                "material" : "lithium",
                "moreUses" : [
                        "Cinematography",
                        "Wildlife Watching",
                        "Precision Agriculture",
                        "Land Inspection",
                        "Water Inspection",
                        "Videography"
                ]
        },
        "utility" : [
                "Recreation",
                "Photography",
                "Videography",
                "Delivery",
                "Natural Resource Exploration"
        ]
}
{
        "_id" : ObjectId("619924ef4b1ff00cfd5eb3ae"),
        "onSale" : true,
        "name" : "Caminara Frucha FCZ-89",
        "price" : 67800,
        "weight" : "35 kilograms",
        "additionalDetails" : {
                "material" : "glass fibre",
                "moreUses" : [ ]
        },
        "utility" : [
                "Recreation",
                "Photography",
                "Videography",
                "Delivery",
                "Natural Resource Exploration"
        ]
}
{
        "_id" : ObjectId("619924ef4b1ff00cfd5eb3af"),
        "onSale" : true,
        "name" : "Azehsra Chelske AZC-HYU56",
        "price" : 45600,
        "weight" : "32 kilograms",
        "additionalDetails" : {
                "material" : "carbon fibre",
                "moreUses" : [
                        "Cinematography",
                        "Wildlife Watching",
                        "Precision Agriculture"
                ]
        },
        "utility" : null
}
{
        "_id" : ObjectId("619924ef4b1ff00cfd5eb3b0"),
        "onSale" : true,
        "name" : "Hazela Gunce QY999",
        "price" : 33900,
        "weight" : "20 kilograms",
        "additionalDetails" : {
                "material" : "carbon fibre",
                "moreUses" : [
                        "Videography",
                        "Wildlife Watching",
                        "Photography"
                ]
        },
        "utility" : [
                undefined
        ]
}

Counting Documents Using $group by Querying on Top-Level Field

In this example, I will provide 3 different examples of counting documents of a collection and grouping them based on some criteria provided to the $group pipeline stage.

Example 1:

Counting documents by grouping them based on their prices.

> db.drones.aggregate([
...  {"$group":{_id:"$price",counter:{$sum:1}}}])
{ "_id" : 33900, "counter" : 1 }
{ "_id" : 99500, "counter" : 1 }
{ "_id" : 120500, "counter" : 1 }
{ "_id" : 15000, "counter" : 1 }
{ "_id" : 55000, "counter" : 1 }
{ "_id" : 77500, "counter" : 1 }
{ "_id" : 23500, "counter" : 1 }
{ "_id" : 67800, "counter" : 1 }
{ "_id" : 110000, "counter" : 1 }
{ "_id" : 45600, "counter" : 1 }
{ "_id" : 87600, "counter" : 1 }

Example 2:

Counting documents by grouping them based on their names.

> db.drones.aggregate([
...  {"$group":{_id:"$name",counter:{$sum:1}}}]);
{ "_id" : "PlayGarra 2078-56", "counter" : 1 }
{ "_id" : "Sifinist Croma AX", "counter" : 1 }
{ "_id" : "Drovce Finnifield FR-7", "counter" : 1 }
{ "_id" : "Azehsra Chelske AZC-HYU56", "counter" : 1 }
{ "_id" : "Frinty Gemini 3X70", "counter" : 1 }
{ "_id" : "Rilche Gabbana Flier RG950", "counter" : 1 }
{ "_id" : "Caminara Frucha FCZ-89", "counter" : 1 }
{ "_id" : "X-Strimmer Eye", "counter" : 1 }
{ "_id" : "Nimbari Gryphon Medeta 65", "counter" : 1 }
{ "_id" : "Khai Balemosh Shefqa TRX", "counter" : 1 }
{ "_id" : "Hazela Gunce QY999", "counter" : 1 }

Example 2:

Counting documents by grouping them based on the onSale field to see how many are false and how many are true.

> db.drones.aggregate([
...  {"$group":{_id:"$onSale",counter:{$sum:1}}}])
{ "_id" : false, "counter" : 5 }
{ "_id" : true, "counter" : 6 }

Counting Documents Using $group by Querying on Nested Field

In this example, I will give you an example of counting documents and grouping them by operating on a nested field. I will be using dot notation to access the nested document or field.

Example 1:

Counting documents and grouping them based on the material nested field.

> db.drones.aggregate([
...  {"$group":{_id:"$additionalDetails.material",counter:{$sum:1}}}])
{ "_id" : "glass fiber", "counter" : 1 }
{ "_id" : "lithium", "counter" : 2 }
{ "_id" : "polysterene", "counter" : 2 }
{ "_id" : "carbon fibre", "counter" : 2 }
{ "_id" : "carbon fiber", "counter" : 1 }
{ "_id" : "aluminum", "counter" : 2 }
{ "_id" : "glass fibre", "counter" : 1 }

Great! With this, we have learned to use the aggregate $group stage to count and group documents.

Conclusion

Learn to select documents by grouping and them and counting them in MongoDB with this easy guide.

References

https://docs.mongodb.com/manual/reference/operator/aggregation/group/#mongodb-pipeline-pipe.-group

https://stackoverflow.com/questions/23116330/mongodb-select-count-group-by