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" : "bibin@me.co",
"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" : "cs@nana.co",
"modifiedBy" : "cs@nana.co",
"creationTime" : NumberLong(1456980946865),
"modificationTime" : NumberLong(1457326319159)
}
/* 2 */
{
"_id" : ObjectId("56e6e6ecb5f1fbfb92892d20"),
"_class" : "com.rating.SellerRate",
"email" : "mimi@me.co",
"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" : "cs@nana.co",
"modifiedBy" : "cs@nana.co",
"creationTime" : NumberLong(1456980946865),
"modificationTime" : NumberLong(1457326319159)
}
Expected Result :
[
{
"email":"bibin@me.co",
"rates":[
{
"param":"SERVICE",
"rate":2.100000023841858
},
{
"param":"QUALITY",
"rate":2.25
}
]
},
{
"email":"mimi@me.co",
"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":"bibin@me.co",
"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":"bibin@me.co",
"orders":{
"orderId":"O1GTLAQWKI",
"rates":[
{
"param":"QUALITY",
"rate":2.7999999523162842
},
{
"param":"SERVICE",
"rate":1.0000000000000000
}
]
}
},
{
"email":"bibin@me.co",
"orders":{
"orderId":"OGHYJUKL",
"rates":[
{
"param":"QUALITY",
"rate":1.7000000476837158
},
{
"param":"SERVICE",
"rate":3.2000000476837158
}
]
}
}
]
}
$project
Operasi $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" : "bibin@me.co",
"param" : "QUALITY",
"rate" : 2.7999999523162842
},
{
"_id" : ObjectId("56d7c3d23004aa334327e109"),
"email" : "bibin@me.co",
"param" : "SERVICE",
"rate" : 1.0000000000000000
},
{
"_id" : ObjectId("56d7c3d23004aa334327e109"),
"email" : "bibin@me.co",
"param" : "QUALITY",
"rate" : 1.7000000476837158
},
{
"_id" : ObjectId("56d7c3d23004aa334327e109"),
"email" : "bibin@me.co",
"param" : "SERVICE",
"rate" : 3.2000000476837158
},
{
"_id" : ObjectId("56e6e6ecb5f1fbfb92892d20"),
"email" : "mimi@me.co",
"param" : "QUALITY",
"rate" : 2.7999999999999998
},
{
"_id" : ObjectId("56e6e6ecb5f1fbfb92892d20"),
"email" : "mimi@me.co",
"param" : "SERVICE",
"rate" : 1.0000000000000000
},
{
"_id" : ObjectId("56e6e6ecb5f1fbfb92892d20"),
"email" : "mimi@me.co",
"param" : "QUALITY",
"rate" : 3.3999999999999999
},
{
"_id" : ObjectId("56e6e6ecb5f1fbfb92892d20"),
"email" : "mimi@me.co",
"param" : "SERVICE",
"rate" : 4.2999999999999998
}
],
"ok" : 1.0000000000000000
}
$group
Jika 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" : "mimi@me.co",
"param" : "SERVICE"
},
"rate" : 2.6499999999999999
},
{
"_id" : {
"email" : "bibin@me.co",
"param" : "SERVICE"
},
"rate" : 2.1000000238418579
},
{
"_id" : {
"email" : "mimi@me.co",
"param" : "QUALITY"
},
"rate" : 3.0999999999999996
},
{
"_id" : {
"email" : "bibin@me.co",
"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.
Dari contoh query di bawah ini, dari hasil query sebelum nya, bahwa email dijadikan sebagai key, dan di push sebagai root dan attribut yang lain otomatis sebagai child (list of array).
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 :