Cara menggunakan mongodb group by count
Singkatnya, proses agregasi dalam mongodb adalah memproses data (record) untuk dikumpulkan menjadi satu atau dipisah-pisah. MongoDB menyinpan data dalam format JSON, dalam kasus kali ini data disimpan dengan format mempunyai format seperti dibawah ini. Data rating untuk masing-masing email (email == unique) dimana 1 email mempunyai child order, dan masing-masing order mempunyai child parameter dan nilai rating di setiap order. /* 1 */ { "_id" : ObjectId("56d7c3d23004aa334327e109"), "_class" : "com.rating.SellerRate", "email" : "", "orders" : [ { "orderId" : "O1GT34QWKI", "rates" : [ { "param" : "QUALITY", "rate" : 2.7999999523162842 }, { "param" : "SERVICE", "rate" : 1.0000000000000000 } ] }, { "orderId" : "OGHYJUKL", "rates" : [ { "param" : "QUALITY", "rate" : 1.7000000476837158 }, { "param" : "SERVICE", "rate" : 3.2000000476837158 } ] } ], "date" : "20160303", "createdBy" : "", "modifiedBy" : "", "creationTime" : NumberLong(1456980946865), "modificationTime" : NumberLong(1457326319159) } /* 2 */ { "_id" : ObjectId("56e6e6ecb5f1fbfb92892d20"), "_class" : "com.rating.SellerRate", "email" : "", "orders" : [ { "orderId" : "O1C45HWKI", "rates" : [ { "param" : "QUALITY", "rate" : 2.7999999999999998 }, { "param" : "SERVICE", "rate" : 1.0000000000000000 } ] }, { "orderId" : "OF46JKJUKL", "rates" : [ { "param" : "QUALITY", "rate" : 3.3999999999999999 }, { "param" : "SERVICE", "rate" : 4.2999999999999998 } ] } ], "date" : "20160303", "createdBy" : "", "modifiedBy" : "", "creationTime" : NumberLong(1456980946865), "modificationTime" : NumberLong(1457326319159) } Expected Result : [ { "email":"", "rates":[ { "param":"SERVICE", "rate":2.100000023841858 }, { "param":"QUALITY", "rate":2.25 } ] }, { "email":"", "rates":[ { "param":"SERVICE", "rate":2.7 }, { "param":"QUALITY", "rate":1.5 } ] } ] Berikut, kurang lebih Query yang harus dijalankan agar mendapat hasil seperti yang di inginkan. db.seller_rates.aggregate([ { $unwind : "$orders" }, { $unwind : "$orders.rates" }, { $project : { email : "$email", param : "$orders.rates.param", rate : "$orders.rates.rate" } }, { $group : { _id : { email : "$email", param : "$param" }, rate : { $avg : "$rate" } } }, { $project : { email : "$_id.email", param : "$_id.param", rate : "$rate" } }, { $group : { _id : "$email", rates : { $push : "$$ROOT" } } } ]) $unwind$unwind merupakan perintah untuk mengeluarkan child menjadi row. Dalam kasus ini 1 email mempunyai 2 orders, maka jika operasi { "$unwind" : "$orders"} gambaran singkatnya kurang lebih digambarkan dibawah ini, data awal : { "email":"", "orders":[ { "orderId":"O1GT34QWKI", "rates":[ { "param":"QUALITY", "rate":2.7999999523162842 }, { "param":"SERVICE", "rate":1.0000000000000000 } ] }, { "orderId":"OGHYJUKL", "rates":[ { "param":"QUALITY", "rate":1.7000000476837158 }, { "param":"SERVICE", "rate":3.2000000476837158 } ] } ] } Hasil $unwind : {
"result":[
{
"email":"",
"orders":{
"orderId":"O1GTLAQWKI",
"rates":[
{
"param":"QUALITY",
"rate":2.7999999523162842
},
{
"param":"SERVICE",
"rate":1.0000000000000000
}
]
}
},
{
"email":"",
"orders":{
"orderId":"OGHYJUKL",
"rates":[
{
"param":"QUALITY",
"rate":1.7000000476837158
},
{
"param":"SERVICE",
"rate":3.2000000476837158
}
]
}
}
]
}
$projectOperasi $project memungkinkan kita hanya memilih spesifik field yang akan ditampilkan. Contoh jika Query nya demikian (dengan menggunakan data existing). db.seller_rates.aggregate([ { $unwind : "$orders" }, { $unwind : "$orders.rates" }, { $project : { email : "$email", param : "$orders.rates.param", rate : "$orders.rates.rate" } } ]) Hasil $project : /* 1 */ { "result" : [ { "_id" : ObjectId("56d7c3d23004aa334327e109"), "email" : "", "param" : "QUALITY", "rate" : 2.7999999523162842 }, { "_id" : ObjectId("56d7c3d23004aa334327e109"), "email" : "", "param" : "SERVICE", "rate" : 1.0000000000000000 }, { "_id" : ObjectId("56d7c3d23004aa334327e109"), "email" : "", "param" : "QUALITY", "rate" : 1.7000000476837158 }, { "_id" : ObjectId("56d7c3d23004aa334327e109"), "email" : "", "param" : "SERVICE", "rate" : 3.2000000476837158 }, { "_id" : ObjectId("56e6e6ecb5f1fbfb92892d20"), "email" : "", "param" : "QUALITY", "rate" : 2.7999999999999998 }, { "_id" : ObjectId("56e6e6ecb5f1fbfb92892d20"), "email" : "", "param" : "SERVICE", "rate" : 1.0000000000000000 }, { "_id" : ObjectId("56e6e6ecb5f1fbfb92892d20"), "email" : "", "param" : "QUALITY", "rate" : 3.3999999999999999 }, { "_id" : ObjectId("56e6e6ecb5f1fbfb92892d20"), "email" : "", "param" : "SERVICE", "rate" : 4.2999999999999998 } ], "ok" : 1.0000000000000000 $groupJika dianalogikan dengan MySQL $group sama dengan operasi GROUP BY. Dalam kasus kita kali ini, bahwa ingin menghitung rata-rata rating berdasarkan email dan parameter. Berikut merupakan contoh query nya : db.seller_rates.aggregate([ { $unwind : "$orders" }, { $unwind : "$orders.rates" }, { $project : { email : "$email", param : "$orders.rates.param", rate : "$orders.rates.rate" } }, { $group : { _id : { email : "$email", param : "$param" }, rate : { $avg : "$rate" } } } ]) Hasil $group : /* 1 */ { "result" : [ { "_id" : { "email" : "", "param" : "SERVICE" }, "rate" : 2.6499999999999999 }, { "_id" : { "email" : "", "param" : "SERVICE" }, "rate" : 2.1000000238418579 }, { "_id" : { "email" : "", "param" : "QUALITY" }, "rate" : 3.0999999999999996 }, { "_id" : { "email" : "", "param" : "QUALITY" }, "rate" : 2.2500000000000000 } ], "ok" : 1.0000000000000000 } Pada hasil Query di atas, terlihat bahwa ada attribute “_id” selalu muncul, atibut ini yang digunakan sebagai key dalam operasi GROUP BY dan field “_id” merupakan mandatory ketika melakukan operasi GROUP BY. $push$push merupakan operasi di
dalam $group, satu level dengan $avg, $max, $sum, dll. Operasi $push akan mengembalikan array dari semua value dengan key yang sama. db.seller_rates.aggregate([ { $unwind : "$orders" }, { $unwind : "$orders.rates" }, { $project : { email : "$email", param : "$orders.rates.param", rate : "$orders.rates.rate" } }, { $group : { _id : { email : "$email", param : "$param" }, rate : { $avg : "$rate" } } }, { $project : { email : "$_id.email", param : "$_id.param", rate : "$rate" } }, { $group : { _id : "$email", rates : { $push : "$$ROOT" } } } ]) Hasil akhir : selamat mencoba ^^v |