group by elements of subdocuments with Max N (3) Records in each group with pagination and overall total

chiku fan

unread,

Oct 18, 2016, 12:58:12 PM10/18/16

to mongodb-user

Hi

I have some sample data stored in mongodb 3.2 as given below and want a 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**
Test1,      20.00,     10,          [category="Cooking/", category="Cooking/Beverages",category="Food Receipe/", category="Food Receipe/Bartending"]

Test2,      19.00,     9,          [category="Cooking/", category="Cooking/Beverages", category="Food Receipe/", category="Food Receipe/Bartending"]
Test3,      18.00,     8,         [category="Cooking/", category="Cooking/Beverages", category="Food Receipe/", category="Food Receipe/Bartending"]
Test4,      17.00,     7,        [category="Cooking/", category="Cooking/Beverages", category="Food Receipe/", category="Food Receipe/Bartending"]
Test5,      16.00,     6,       [category="Cooking/", category="Cooking/Beverages", category="Food Receipe/", category="Food Receipe/Bartending"]
Test1,      15.00,     5,      [category="Cooking/", category="Cooking/Beverages", category="Food Receipe/", category="Food Receipe/Bartending"]
Test2,      14.00,     4,     [category="Cooking/", category="Cooking/Beverages", category="Food Receipe/", category="Food Receipe/Bartending"]
Test3,      13.00,     3,    [category="Cooking/", category="Cooking/Beverages", category="Food Receipe/", category="Food Receipe/Bartending"]
Test4,      12.00,     2,   [category="Cooking/", category="Cooking/Beverages", category="Food Receipe/", category="Food Receipe/Bartending"]
Test5,      11.00,     1,  [category="Cooking/", category="Cooking/Beverages", category="Food Receipe/", category="Food Receipe/Bartending"]
Test6,      10.00,     1,          [category="Cooking/", category="Cooking/Beverages",category="Cooking/Beverages/Bartending", category="Food Receipe/", category="Food Receipe/Taste"]
Test1,      11.00,     2,         [category="Cooking/", category="Cooking/Beverages",category="Cooking/Beverages/Bartending", category="Food Receipe/", category="Food Receipe/Taste"]
Test2,      12.00,     3,        [category="Cooking/", category="Cooking/Beverages",category="Cooking/Beverages/Bartending", category="Food Receipe/", category="Food Receipe/Taste"]
Test3,      13.00,     4,       [category="Cooking/", category="Cooking/Beverages",category="Cooking/Beverages/Bartending", category="Food Receipe/", category="Food Receipe/Taste"]
Test4,     14.00,     5,      [category="Cooking/", category="Cooking/Beverages",category="Cooking/Beverages/Bartending", category="Food Receipe/", category="Food Receipe/Taste"]
Test5,     15.00,     6,     [category="Cooking/", category="Cooking/Beverages",category="Cooking/Beverages/Bartending", category="Food Receipe/", category="Food Receipe/Taste"]
Test10,     16.00,     7,          [category="Cooking/", category="Cooking/Beverages",category="Cooking/Beverages/Bartending", category="Food Receipe/", category="Food Receipe/Taste"]
Test11,     17.00,     8,         [category="Cooking/", category="Cooking/Beverages",category="Cooking/Beverages/Bartending", category="Food Receipe/", category="Food Receipe/Taste"]

aggregate query Needed o foutput in this 
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

Please help.

Regards Kris

chiku fan

unread,

Oct 20, 2016, 7:57:38 AM10/20/16

to mongodb-user

Any updates on this issue with mongodb.

Regards
Kris

Lungang Fang

unread,

Oct 25, 2016, 2:06:08 AM10/25/16

to mongodb-user

Hi Chiku,

The input and output you require look like the output of a relational database, so it’s not clear how your requirement maps to MongoDB. Could you elaborate more on your use case in MongoDB terms, e.g.

Some example documents
Some example output
Your MongoDB version.

Regards,
Lungang

Message has been deleted

chiku fan

unread,

Oct 25, 2016, 6:57:36 AM10/25/16

to mongodb-user

Hi Lungang

Some example documents

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

2.Some example output

I have tried this link http://stackoverflow.com/questions/22932364/mongodb-group-values-by-multiple-fields. I want to write a mongodb query to group by elements of sub documents with Max N(3) in each group in descending order of revenue / group with pagination and overall total. Please help me with the query desired.

Sample query I tried is :

db.books.aggregate([
{$match:{'bookCategory.categoryCode' : 'Cooking/'}},
{$unwind:'$bookCategory'}, /* This unwind creating performance problems, with 1M records, with 100 (Book Categories / book) elements in subdocuments causes 100M documents to be retrieved, may cause memory problems for our memory size : 4GB */
{ "$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.

3. Your MongoDB version : Mongodb 3.2 Community Version

Please help.

Regards

Kris

On Tuesday, October 18, 2016 at 10:28:12 PM UTC+5:30, chiku fan wrote:

Lungang Fang

unread,

Nov 8, 2016, 9:12:53 PM11/8/16

to mongodb-user

Hi Kris,

{ $unwind:'$ bookCategory’}, / This unwind creating performance problems, with 1M records, with 100 (Book Categories / book) elements in subdocuments causes 100M documents to be retrieved, may cause memory problems for our memory size : 4GB /

It’s likely that the provisioned memory is too small for the amount of data that you need to $unwind, causing the performance issue you observed. Perhaps Pre-Aggregated Reports is a better choice. Although some implementation details in the link relate to MMAPV1, the idea of pre-aggregated reports is valid for any storage engine. You can, for example, create a pre-aggregated document for each category.

Another advantage of pre-aggregated report is that he can access the report in almost real-time, since the reports are updated every time he inserts a new document

As for pagination, it can be supported by using skip() and limit() on the pre-aggregated report collection.

Note that using pre-aggregated report requires writing into two collections in a single insert. You may take a look at How To Write Resilient MongoDB Applications for more details about how to write a resilient application.

Regards,
Lungang

Reply all

Reply to author

Forward