I've a collection with below documents:
{
"_id" : ObjectId("54acfb67a81bf9509246ed81"),
"Billno" : 1234,
"details" : [
{
"itemcode" : 12,
"itemname" : "Paste100g",
"qty" : 2,
"price" : 50
},
{
"itemcode" : 14,
"itemname" : "Paste30g",
"qty" : 4,
"price" : 70
},
{
"itemcode" : 12,
"itemname" : "Paste100g",
"qty" : 4,
"price" : 100
}
]
}
{
"_id" : ObjectId("54acff86a81bf9509246ed82"),
"Billno" : 1237,
"details" : [
{
"itemcode" : 12,
"itemname" : "Paste100g",
"qty" : 3,
"price" : 75
},
{
"itemcode" : 19,
"itemname" : "dates100g",
"qty" : 4,
"price" : 170
},
{
"itemcode" : 22,
"itemname" : "dates200g",
"qty" : 2,
"price" : 160
}
]
}
I need to display below output. Please help
Required Output:
---------------------------------------------------------------------------------
itemcode itemname totalprice totalqty
---------------------------------------------------------------------------------
12 Paste100g 225 9
14 Paste30g 70 4
19 dates100g 170 4
22 dates200g 160 2
The MongoDB aggregation pipeline is available to solve your problem. You get details out of an array my processing with $unwind and then using $group to "sum" the totals:
db.collection.aggregate([
// Unwind the array to de-normalize as documents
{ "$unwind": "$details" },
// Group on the key you want and provide other values
{ "$group": {
"_id": "$details.itemcode",
"itemname": { "$first": "$details.itemname" },
"totalprice": { "$sum": "$details.price" },
"totalqty": { "$sum": "$details.qty" }
}}
])
Ideally you want a $match stage in there to filter out any irrelevant data first. This is basically MongoDB query and takes all the same arguments and operators.
Most here is simple really. The $unwind is sort of like a "JOIN" in SQL except that in an embedded structure the "join" is already made, so you are just "de-normalizing" like a join would do between "one to many" table relationships but just within the document itself. It basically "repeats" the "parent" document parts to the array for each array member as a new document.
Then the $group works of a key, as in "GROUP BY", where the "key" is the _id value. Everything there is "distinct" and all other values are gathered by "grouping operators".
This is where operations like $first come in. As described on the manual page, this takes the "first" value from the "grouping boundary" mentioned in the "key" earlier. You want this because all values of this field are "likely" to be the same, so this is a logical choice to just pick the "first" match.
Finally there is the $sum grouping operator which does what should be expected. All supplied values under the "key" are "added" or "summed" together to provide a total. Just like SQL SUM().
Also note that all the $ prefixed names there is how the aggregation framework deals with variables for "field/property" names within the current document being processed. "Dot notation" is used to reference the embedded "fields/properties" nested within a parent property name.
It is useful to learn aggregation in MongoDB. It is to general queries what anything beyond a basic "SELECT" statement is to SQL. Not just for "grouping" but for other manipulation as well.
Read through the documentation of all aggregation operators and also take a look a SQL to Aggregation Mapping in the documentation as a general guide if you have some familiarity with SQL to begin with. It helps explain concepts and shows some things that can be done.
