"Structured" grouping query in MongoDB to display result with a new field displaying the count

For this, use $group in MongoDB with aggregate(). The $group stage groups input documents by the specified _id expression and for each distinct grouping, outputs a document. Structured grouping allows you to create complex grouping hierarchies and add calculated fields like counts to your results.

Let us first create a collection with documents −

> db.demo534.insertOne({_id:10,"ProductId":100,"ProductName":"Product-1"});
{ "acknowledged" : true, "insertedId" : 10 }
> db.demo534.insertOne({_id:11,"ProductId":100,"ProductName":"Product-2"});
{ "acknowledged" : true, "insertedId" : 11 }
> db.demo534.insertOne({_id:12,"ProductId":101,"ProductName":"Product-1"});
{ "acknowledged" : true, "insertedId" : 12 }

Display all documents from a collection with the help of find() method −

> db.demo534.find();

This will produce the following output −

{ "_id" : 10, "ProductId" : 100, "ProductName" : "Product-1" }
{ "_id" : 11, "ProductId" : 100, "ProductName" : "Product-2" }
{ "_id" : 12, "ProductId" : 101, "ProductName" : "Product-1" }

Structured Grouping Query

Following is the query to implement structured grouping query in MongoDB. This query uses two $group stages: first to count occurrences of each product name within each product ID, then to structure the results by grouping items under their respective product IDs −

> db.demo534.aggregate([
    {
        $group: {
            _id: {
                productName: "$ProductName",
                productId: "$ProductId"
            },
            count: {
                $sum: 1
            }
        }
    },
    {
        $group: {
            _id: "$_id.productId",
            itemCounts: {
                "$push": {
                    productName: "$_id.productName",
                    count: "$count"
                }
            }
        }
    }
]);

This will produce the following output −

{ "_id" : 101, "itemCounts" : [ { "productName" : "Product-1", "count" : 1 } ] }
{ "_id" : 100, "itemCounts" : [ { "productName" : "Product-2", "count" : 1 }, { "productName" : "Product-1", "count" : 1 } ] }

The first $group stage creates a count for each unique combination of product name and product ID, while the second $group stage organizes these results into a structured format grouped by product ID with an array of product names and their counts.