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

For this, use $group in MongoDB IN aggregate(). The $group groups input documents by the specified _id expression and for each distinct grouping, outputs a document. 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" }

Following is the query to implement structured grouping query in MongoDB −

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