Key words: nested document  multilevel structure  subdocument  summarize  grouping

A nested MongoDB document contains a one-to-many relationship. To group and summarize subdocuments inside a document by category, you need to first detangle the nested structure into single level structure. Intermediate calculations are involved and certain functions are needed to output the result. This can’t be done with the $group operator alone. Over the MongoDB document below, group by category and sum terms and output the result in a descending order.

[
 category: "movies",
 terms: [{term: "movie 1", total: 1000}, {term: "movie 2", total: 100} ]
},
{ category: "sports",
 terms: [{term: "football 1", total: 1000}, {term: "tennis 2", total: 120} ]
},
{ category: "movies",
 terms: [{term: "movie 1", total: 5000}, {term: "movie 2", total: 200},
{term: "movie 3", total: 280} ]
},
{ category: "sports",
 terms: [{term: "football 1", total: 4000}, {term: "tennis 2", total: 250},
{term: "tennis 2", total: 450} ]
},
]

Below is the MongoDB solution using aggregate and $group:
 db.order.aggregate([
 {$unwind : "$terms"},
 { $group : { _id : {
       category: "$category",
       term: "$terms.term" },
       total: {$sum : "$terms.total"}
       }
 },
 {$sort : { total : -1} },
  { $project: {
       _id: 0,
       category: "$_id.category",
       term: "$_id.term",
       total:1}}
])

$unwind splits each array in terms field into multiple documents; $group groups them, perform sum and sort the result; $project performs a filtering and outputs the result.

The process would be simple and clear if we could use esProc to do this for MongDB:

esProc SPL is efficient in help coping with lots of MongoDB computations. More examples can be found in SPL Assists MongoDB Calculation.
SPL is integration-friendly with a Java program. Read How to Call an SPL Script in Java to learn more.

To begin to work with esProc, see Getting Started with esProc.