I have some sample data stored in mongodb 3.2 as given below and want a mongodb / spring-data-mongodb aggregation query to group by elements of subdocuments with Max N (3) Records in each group with pagination and overall total.
**Book Name, Revenue , units sold, Book Categories**
{"_id":{"$oid":"5808578b33fa6f161c9747f8"},"_class":"exceltest.TestBean","bookName":"Test6","revenue":10.0,"unitsSold":1,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"},{"categoryCode":"Cooking/Beverages/Bartending"},{"categoryCode":"Food Receipe/Taste"}]}
{"_id":{"$oid":"5808578b33fa6f161c9747f9"},"_class":"exceltest.TestBean","bookName":"Test1","revenue":11.0,"unitsSold":2,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"},{"categoryCode":"Cooking/Beverages/Bartending"},{"categoryCode":"Food Receipe/Taste"}]}
{"_id":{"$oid":"5808578b33fa6f161c9747fa"},"_class":"exceltest.TestBean","bookName":"Test2","revenue":12.0,"unitsSold":3,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"},{"categoryCode":"Cooking/Beverages/Bartending"},{"categoryCode":"Food Receipe/Taste"}]}
{"_id":{"$oid":"5808578b33fa6f161c9747fb"},"_class":"exceltest.TestBean","bookName":"Test3","revenue":13.0,"unitsSold":4,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"},{"categoryCode":"Cooking/Beverages/Bartending"},{"categoryCode":"Food Receipe/Taste"}]}
{"_id":{"$oid":"5808578b33fa6f161c9747fc"},"_class":"exceltest.TestBean","bookName":"Test4","revenue":14.0,"unitsSold":5,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"},{"categoryCode":"Cooking/Beverages/Bartending"},{"categoryCode":"Food Receipe/Taste"}]}
{"_id":{"$oid":"5808578b33fa6f161c9747fd"},"_class":"exceltest.TestBean","bookName":"Test5","revenue":15.0,"unitsSold":6,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"},{"categoryCode":"Cooking/Beverages/Bartending"},{"categoryCode":"Food Receipe/Taste"}]}
{"_id":{"$oid":"5808578b33fa6f161c9747fe"},"_class":"exceltest.TestBean","bookName":"Test10","revenue":16.0,"unitsSold":7,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"},{"categoryCode":"Cooking/Beverages/Bartending"},{"categoryCode":"Food Receipe/Taste"}]}
{"_id":{"$oid":"5808578b33fa6f161c9747ff"},"_class":"exceltest.TestBean","bookName":"Test11","revenue":100.0,"unitsSold":100,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"},{"categoryCode":"Cooking/Beverages/Bartending"},{"categoryCode":"Food Receipe/Taste"}]}
{"_id":{"$oid":"580857b833fa6f0c3499e462"},"_class":"exceltest.TestBean","bookName":"Test1","revenue":20.0,"unitsSold":10,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"}]}
{"_id":{"$oid":"580857b833fa6f0c3499e463"},"_class":"exceltest.TestBean","bookName":"Test2","revenue":19.0,"unitsSold":9,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"}]}
{"_id":{"$oid":"580857b833fa6f0c3499e464"},"_class":"exceltest.TestBean","bookName":"Test3","revenue":18.0,"unitsSold":8,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"}]}
{"_id":{"$oid":"580857b833fa6f0c3499e465"},"_class":"exceltest.TestBean","bookName":"Test4","revenue":17.0,"unitsSold":7,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"}]}
{"_id":{"$oid":"580857b833fa6f0c3499e466"},"_class":"exceltest.TestBean","bookName":"Test5","revenue":16.0,"unitsSold":6,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"}]}
{"_id":{"$oid":"580857b833fa6f0c3499e467"},"_class":"exceltest.TestBean","bookName":"Test1","revenue":15.0,"unitsSold":5,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"}]}
{"_id":{"$oid":"580857b833fa6f0c3499e468"},"_class":"exceltest.TestBean","bookName":"Test2","revenue":14.0,"unitsSold":4,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"}]}
{"_id":{"$oid":"580857b833fa6f0c3499e469"},"_class":"exceltest.TestBean","bookName":"Test3","revenue":13.0,"unitsSold":3,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"}]}
{"_id":{"$oid":"580857b833fa6f0c3499e46a"},"_class":"exceltest.TestBean","bookName":"Test4","revenue":12.0,"unitsSold":2,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"}]}
{"_id":{"$oid":"580857b833fa6f0c3499e46b"},"_class":"exceltest.TestBean","bookName":"Test5","revenue":11.0,"unitsSold":1,"bookCategory":[{"categoryCode":"Cooking/"},{"categoryCode":"Cooking/Beverages"},{"categoryCode":"Food Receipe/"},{"categoryCode":"Food Receipe/Bartending"}]}
aggregate query Needed the output in this for categoryCode :"COOKING"
Overall Total Revenue : 263
Overall units sold : 91
Cooking/Beverages
/* 3 top book names for category Cooking/Beverages sorted in descending order*/
Book Name | Total Revenue | Total units
Test11 | 100 | 100
Test1 | 46 | 17
Test2 | 45 | 16
/* Next 3 top book names in descending order with pagination*/
Test3 | 44 | 15
Test4 | 43 | 14
Test5 | 42 | 13
/* Next 3 top book names in descending order with Pagination*/
Test10 | 16 | 7
/* 3 top book names for Cooking/Beverages/Bartending sorted in descending order*/
Book Name | Total Revenue | Total units
Test11 | 100 | 100
Test1 | 46 | 17
Test2 | 45 | 16
/* Next 3 top book names in descending order with pagination*/
Test3 | 44 | 15
Test4 | 43 | 14
Test5 | 42 | 13
/* Next 3 top book names in descending order with pagination*/
Test10 | 16 | 7
I have tried this link mongodb group values by multiple fields Sample query I tried is :
db.books.aggregate([
{$match:{'bookCategory.categoryCode' : 'Cooking/'}},
{$unwind:'$bookCategory'},
{ "$group": {
"_id": {
"categoryCode": "$bookCategory.categoryCode",
"book": "$bookName"
},
"revenue": { $sum:"$revenue" },
"unitsSold": { $sum:"$unitsSold" }
}
}
,
{$match:{'_id.categoryCode' : {$regex : 'Cooking/'}}},
{ "$group": {
"_id": "$_id.categoryCode",
"books": {
"$push": {
// "category": "$_id.categoryCode",
"book":"$_id.book",
"revenue": { $sum:"$revenue" },
"unitsSold": { $sum:"$unitsSold" }
},
},
"topRevenue": { $sum: "$revenue" },
"topUnitsSold": { $sum:"$unitsSold" }
}},
{ "$sort": { "topRevenue": -1 } },
{ "$limit": 3},
{ "$project": {
"books": { "$slice": [ "$books", 3 ] },
"topRevenue": 1,
"topUnitsSold": 1
}}
])
The output is as follows :
{
"_id" : "Cooking/",
"books" : [
{
"book" : "Test6",
"revenue" : 10,
"unitsSold" : 1
},
{
"book" : "Test1",
"revenue" : 46,
"unitsSold" : 17
},
{
"book" : "Test4",
"revenue" : 43,
"unitsSold" : 14
}
],
"topRevenue" : 263,
"topUnitsSold" : 91
}
{
"_id" : "Cooking/Beverages",
"books" : [
{
"book" : "Test6",
"revenue" : 10,
"unitsSold" : 1
},
{
"book" : "Test1",
"revenue" : 46,
"unitsSold" : 17
},
{
"book" : "Test2",
"revenue" : 45,
"unitsSold" : 16
}
],
"topRevenue" : 263,
"topUnitsSold" : 91
}
{
"_id" : "Cooking/Beverages/Bartending",
"books" : [
{
"book" : "Test6",
"revenue" : 10,
"unitsSold" : 1
},
{
"book" : "Test2",
"revenue" : 12,
"unitsSold" : 3
},
{
"book" : "Test1",
"revenue" : 11,
"unitsSold" : 2
}
],
"topRevenue" : 108,
"topUnitsSold" : 36
}
but I am NOT successful in getting the desired result.
Please lemme know about the feasibility of this requirement in mongodb.
Please help.
Regards Kris
