The document-based NoSQL database MongoDB is popular worldwide thanks to its powerful processing ability. Besides the basic insert, delete, update and query, it also supports the relatively complicated grouping and aggregate operation. Yet in computing field there is always a better option. Here we’ll compare MongoDB method and SPL (Structured Process Language) way of grouping and summarizing data stored in MongoDB in several scenarios.

 

1. Nested array query

Task: Calculate average score of all subjects and each student’s total score.

Sample data:

_id	name	Gender	Score
1	Tom	F	[{"subject":"  Physics","mark":60  },    {"subject":"Chemical","mark":72  }]
2	Jerry	M	[{"subject":"  Physics","mark":92  },    {"subject":"  Math","mark":81}]

Expected result:

Physics	76		Tom	132
Chemical	72		Jerry	173
Math	81

Mongodb script:

db.student.aggregate( [ 
   {$unwind : "score"},
  {score"},
  {score"},
  {group: {
   "_id":   {"name"  :"name"}   , 
   "qty":{"\$sum"   :  "name"}   , 
   "qty":{"\$sum"   :  "name"}   , 
   "qty":{"\$sum"   :  "score.mark"}
   }
  }
  ] )

        

db.student.aggregate( [    {unwind : "score"},   {unwind : "score"},   {unwind : "score"},   {group: {    "_id":  {"subject":"score.subject"}   ,     "qty":{"\$avg":   "score.subject"}   ,     "qty":{"\$avg":   "score.subject"}   ,     "qty":{"\$avg":   "score.mark"}    }   }   ] )

The score field contains arrays of [{subject, mark},…] structure. They need to be split to map on to the student to be further processed usingscore"},
  {group: {
   "_id":  {"subject":"group: {
   "_id":  {"subject":"group: {
   "_id":  {"subject":"score.subject"}   , 
   "qty":{"$avg":   "score.mark"}
   }
  }
  ] )

   

db.student.aggregate( [ 
   {\$unwind : "score.mark"}
   }
  }
  ] )

   

db.student.aggregate( [ 
   {\$unwind : "score.mark"}
   }
  }
  ] )

   

db.student.aggregate( [ 
   {\$unwind : "score"},
  {group: {
   "_id":   {"name"  :"group: {
   "_id":   {"name"  :"group: {
   "_id":   {"name"  :"name"}   , 
   "qty":{"$sum"   :  "score.mark"}
   }
  }
  ] )

   

The score field contains arrays of [{subject, mark},…] structure. They need to be split to map on to the student to be further processed usingscore"},
  {score.mark"}
   }
  }
  ] )

   

The score field contains arrays of [{subject, mark},…] structure. They need to be split to map on to the student to be further processed usingscore"},
  {score.mark"}
   }
  }
  ] )

   

The score field contains arrays of [{subject, mark},…] structure. They need to be split to map on to the student to be further processed usingscore"},
  {group: {
   "_id":  {"subject":"score.subject"}   , 
   "qty":{"\$avg":   "score.subject"}   , 
   "qty":{"\$avg":   "score.subject"}   ,
   "qty":{"\$avg":   "score.mark"}
   }
  }
  ] )

db.student.aggregate( [
   {$unwind : "score"},
  {score"},
  {score"},
  {group: {
   "_id":   {"name"  :"name"}   , 
   "qty":{"\$sum"   :  "name"}   , 
   "qty":{"\$sum"   :  "name"}   ,
   "qty":{"\$sum"   :  "score.mark"}
   }
  }
  ] )

The score field contains arrays of [{subject, mark},…] structure. They need to be split to map on to the student to be further processed using unwind operator and group() method.

SPL script (student.dfx):

	A	B
1	=mongo_open("mongodb://127.0.0.1:27017/raqdb")
2	=mongo_shell(A1,"student.find()").fetch()
3	=A2.conj(score).groups(subject:SUBJECT;avg(mark):AVG)
4	=A2.new(name:NAME,score.sum(mark):TOTAL)
5	>A1.close()

Average score for each subject:

SUBJECT	AVG
Chemical	72.0
Math	81.0
Physics	76.0

Total score of each student:

NAME	TOTAL
Tom	132
Jerry	173

 

Script explanation:
A1: Connect to mongodb database.
A2: Read in data from student collection.
A3: Split score field and union it into a table sequence, and group the table by subject to calculate the average score.
A4: Calculate each student’s total score and return a new table sequence consisting of NAME and TOTAL fields. new() function creates a new table sequence.
A5: Close mongodb connection.

 

2. Nested document query

Task: Calculate sum of quantities in every income value and output value in each document.
Sample data:

_id	income	output
1	｛"cpu":1000, "mem":500,  "mouse":"100"｝	｛"cpu":1000, "mem":600   ,"mouse":"120"｝
2	｛"cpu":2000, "mem":1000,    "mouse":"50","mainboard":500   ｝	{"cpu":1500,   "mem":300}

Expected result:

_id	income	output
1	1600	1720
2	3550	1800

Mongodb script:

    

var fields = [  "income", "output"];   db.computer.aggregate([    {      project:{        "values":{           filter:{            input:{               "\$objectToArray":"filter:{            input:{               "\$objectToArray":"filter:{            input:{               "\$objectToArray":"ROOT"           },           cond:{              ROOT"           },           cond:{              ROOT"           },           cond:{              in:[               "this.k",              fields             ]            }           }          }      }     },     {      \$unwind:"$values"     },     {        $project:{        key:"$values.k",        values:{           "$sum":{            "$let":{              "vars":{                "item":{                  "\$objectToArray":"$values.v"               }             },              "in":"this.k",              fields             ]            }           }          }      }     },     {      \$unwind:"$values"     },     {        $project:{        key:"$values.k",        values:{           "$sum":{            "$let":{              "vars":{                "item":{                  "\$objectToArray":"$values.v"               }             },              "in":"this.k",              fields             ]            }           }          }      }     },     {      \$unwind:"$values"     },     {        $project:{        key:"$values.k",        values:{           "$sum":{            "$let":{              "vars":{                "item":{                  "\$objectToArray":"$values.v"               }             },              "in":"item.v"            }          }         }       }     },    {sort: {"_id":-1}},     { "sort: {"_id":-1}},     { "sort: {"_id":-1}},     { "group": {     "_id": "_id",     'income':{"\$first":  "_id",     'income':{"\$first":  "_id",     'income':{"\$first":  "values"},     "output":{"$last":    "values"}     }},   ]);

project:{ 
      "values":{   
      values"}
    }},
  ]);

   

project:{ 
      "values":{   
      values"}
    }},
  ]);

   

project:{ 
      "values":{   
       filter:{ 
          input:{ 
             "$objectToArray":"ROOT"
          },
          cond:{ 
            $in:[ 
             "ROOT"
          },
          cond:{ 
            $in:[ 
             "ROOT"
          },
          cond:{ 
            $in:[ 
             "this.k",
             fields
            ]
           }
          }
         }
     }
    },
    {
     $unwind:"values"
    },
    { 
     values"
    },
    { 
     values"
    },
    { 
      project:{
       key:"values.k",
       values:{ 
         "values.k",
       values:{ 
         "values.k",
       values:{ 
         "sum":{ 
          "let":{ 
            "vars":{ 
              "item":{
                 "\$objectToArray":"let":{ 
            "vars":{ 
              "item":{
                 "\$objectToArray":"let":{ 
            "vars":{ 
              "item":{
                 "\$objectToArray":"values.v"
              }
            },
             "in":"item.v"
           }
         }
        }
      }
    },
   {$sort: {"_id":-1}},
    { "$group": {
    "_id": "$_id",
    'income':{"\$first":  "$values"},
    "output":{"\$last":    "$values"}
    }},
  ]);

   

project:{ 
      "values":{   
       $filter:{ 
          input:{ 
             "\$objectToArray":"item.v"
           }
         }
        }
      }
    },
   {$sort: {"_id":-1}},
    { "$group": {
    "_id": "$_id",
    'income':{"\$first":  "$values"},
    "output":{"\$last":    "$values"}
    }},
  ]);

   

project:{ 
      "values":{   
       $filter:{ 
          input:{ 
             "\$objectToArray":"item.v"
           }
         }
        }
      }
    },
   {$sort: {"_id":-1}},
    { "$group": {
    "_id": "$_id",
    'income':{"\$first":  "$values"},
    "output":{"\$last":    "$values"}
    }},
  ]);

   

project:{
      "values":{   
       $filter:{
          input:{
             "\$objectToArray":"ROOT"
          },
          cond:{
            in:[ 
             "in:[ 
             "in:[
             "this.k",
             fields
            ]
           }
          }
         }
     }
    },
    {
     \$unwind:"this.k",
             fields
            ]
           }
          }
         }
     }
    },
    {
     \$unwind:"this.k",
             fields
            ]
           }
          }
         }
     }
    },
    {
     \$unwind:"values"
    },
    {
      project:{
       key:"project:{
       key:"project:{
       key:"values.k",
       values:{
         "sum":{ 
          "sum":{ 
          "sum":{
          "let":{
            "vars":{
              "item":{
                 "$objectToArray":"values.v"
              }
            },
             "in":"values.v"
              }
            },
             "in":"values.v"
              }
            },
             "in":"item.v"
           }
         }
        }
      }
    },
   {item.v"
           }
         }
        }
      }
    },
   {item.v"
           }
         }
        }
      }
    },
   {sort: {"_id":-1}},
    { "group": {
    "_id": "group": {
    "_id": "group": {
    "_id": "_id",
    'income':{"$first":  "values"},
    "output":{"\$last":    "values"},
    "output":{"\$last":    "values"},
    "output":{"\$last":    "values"}
    }},
  ]);

filter operator stores income field and output field as arrays. unwindoperatorspliteacharrayintoarecord.unwindoperatorspliteacharrayintoarecord.unwind operator split each array into a record. sum operator calculates sum of members. group operator groups data by _id and joins up values.

SPL script:

      
   

A

   

B

       

1

   

=mongo_open("mongodb://127.0.0.1:27017/raqdb")

   
       

2

   

=mongo_shell(A1,"computer.find()").fetch()

   
       

3

   

=A2.new(_id:ID,income.array().sum():INCOME,output.array().sum():OUTPUT)

   
       

4

   

>A1.close()

   
   

Result:

      

ID

   

INCOME

   

OUTPUT

       

1

   

1600.0

   

1720.0

       

2

   

3550.0

   

1800.0

   

Script explanation:
A1: Connect to mongodb database.
A2: Read in data from computer collection.
A3: Split each income value and output value into a sequence and perform sum and join results up with corresponding ID to form a new table sequence.
A4: Close database connection.

SPL gets field values from each subdocument and calculates their sum. This is much simpler. Beside, a nested document looks like a nested array at first sight, so be careful when you write the script.

 

3. Group and aggregate by segments

Task: Divide the following data by specified intervals of sales and count records in each segment.

Sample data:

_id	NAME	STATE	SALES
1	Ashley	New York	11000
2	Rachel	Montana	9000
3	Emily	New York	8800
4	Matthew	Texas	8000
5	Alexis	Illinois	14000

Grouping conditions: [0,3000);[3000,5000);[5000,7500);[7500,10000);[10000, ∞)

Expected result:

Segment	number
3	3
4	2

MongoDB script:

var a_count=0;   var b_count=0;   var c_count=0;   var d_count=0;   var e_count=0;   db.sales.find({   }).forEach(     function(myDoc) {       if (myDoc.SALES <3000)  {          a_count += 1;        }        else if (myDoc.SALES <5000)  {         b_count += 1;        }        else if (myDoc.SALES  <7500) {         c_count += 1;        }       else if (myDoc.SALES  <10000) {         d_count += 1;       }       else {         e_count += 1;       }      }     );     print("a_count="+a_count)   print("b_count="+b_count)   print("c_count="+c_count)   print("d_count="+d_count)   print("e_count="+e_count)

MongoDB lacks related API to handle grouping according to specified intervals and the hardcoding process is roundabout. The above script is just one of the Mongo solutions.

SPL script:

	A	B
1	[3000,5000,7500,10000,15000]
2	=mongo_open("mongodb://127.0.0.1:27017/raqdb")
3	=mongo_shell(A2,"sales.find()").fetch()
4	=A3.groups(A1.pseg(int(~.SALES)):Segment;count(1):    number)
5	>A2.close()

Script explanation:
A1: Define intervals by which SALES is segmented.
A2: Connect to mongodb database.
A3: Read in data from sales table.
A4: Group records by sales intervals and count records in each group. pseg() function returns the sequence number of a group where a records falls in. int() function converts data into an integer.
A5: Close Mongodb connection.

Both scripts get the desired result, but SPL script is more concise by using pseg() function.

 

4. Multilevel group and aggregate

Task: Group the following data by addr and then by book and count books for each addr and those for each book type.

addr	book
address1	book1
address2	book1
address1	book5
address3	book9
address2	book5
address2	book1
address1	book1
address15	book1
address4	book3
address5	book1
address7	book11
address1	book1

Expected result:

_id	Total	books	Count
address1	4	book1	3
		book5	1
address15	1	book1	1
address2	3	book1	2
		book5	1
address3	1	book9	1
address4	1	book3	1
address5	1	book1	1
address7	1	book11	1

MongoDB script:

db.books.aggregate([    {    "group": {     "_id": {     "addr": "addr",     "book": "addr",     "book": "addr",     "book": "book"    },     "bookCount":   {"sum": 1}    }},    {    "group": {     "_id":   "group": {     "_id":   "group": {     "_id":   "_id.addr",     "books": {      "push": {      "book": "push": {      "book": "push": {      "book": "_id.book",     "count": "bookCount"    },        },         "count": {"\$sum":   "bookCount"    },        },         "count": {"\$sum":   "bookCount"    },        },         "count": {"\$sum":   "bookCount"}      }},      {"sort":  { "count": -1} },      {  "sort":  { "count": -1} },      {  "sort":  { "count": -1} },      {  "project": {         "books": {"sum": 1}    }},    {    "group": {     "_id":   "group": {     "_id":   "group": {     "_id":   "_id.addr",     "books": {      "push": {      "book": "push": {      "book": "push": {      "book": "_id.book",     "count": "bookCount"    },        },         "count": {"\$sum":   "bookCount"    },        },         "count": {"\$sum":   "bookCount"    },        },         "count": {"\$sum":   "bookCount"}      }},      {"sort":  { "count": -1} },      {  "sort":  { "count": -1} },      {  "sort":  { "count": -1} },      {  "project": {         "books": {"sum": 1}    }},    {    "group": {     "_id":   "group": {     "_id":   "group": {     "_id":   "_id.addr",     "books": {     "push": {      "book": "push": {      "book": "push": {     "book": "_id.book",     "count": "bookCount"    },        },         "count": {"\$sum":   "bookCount"    },        },         "count": {"\$sum":   "bookCount"    },        },         "count": {"\$sum":   "bookCount"}      }},      {"sort":  { "count": -1} },      {  "sort":  { "count": -1} },      {  "sort":  { "count": -1} },      {  "project": {         "books": {"slice": [   "$books", 2] },         "count": 1      }}   ]).pretty()

The above script groups data by addr,book and count books, and then re-group data by addr to count books, and display data in desired format.

SPL script (books.dfx):

	A	B
1	=mongo_open("mongodb://127.0.0.1:27017/raqdb")
2	=mongo_shell(A1,"books.find()")
3	=A2.groups(addr,book;count(book):     Count)
4	=A3.groups(addr;sum(Count):Total)
5	=A3.join(addr,A4:addr,Total)	return A5
6	>A1.close()

Result:

Address	book	Count	Total
address1	book1	3	4
address1	book5	1	4
address15	book1	1	1
address2	book1	2	3
address2	book5	1	3
address3	book9	1	1
address4	book3	1	1
address5	book1	1	1
address7	book11	1	1

Script explanation:
A1: Connect to mongodb database.
A2: Read in data from books collection.
A3: Group data by addr&book and count books in each group.
A4: Re-group data by addr to count books in each group.
A5: Add A4’s Total field to A3’s table sequence by addr values.
B5: Return A5’s table sequence.
A6: Close database connection.

The above SPL script is both concise and integration-friendly.

To handle the grouping and aggregation in Java, you need to implement the requirement from the low level. The code is complicated and can’t be used to handle other scenarios. esProc offers JDBC interface to enable a Java application to call the SPL script in the manner of calling a stored procedure. See esProc JDBC Basic Uses to learn more.

Here’s how Java calls an SPL script:
   public void testStudent (){
        Connection con = null;
       com.esproc.jdbc.InternalCStatement st;
    try{
       // Establish a connection
       Class.forName("com.esproc.jdbc.InternalDriver");
       con= DriverManager.getConnection("jdbc:esproc:local://");
       // Call the stored procedure, where books is the SPL script (with dfx extension) name
       st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call books ()");
       // Execute the stored procedure
       st.execute();
       // Get the result set
       ResultSet rs = st.getResultSet();
        
     catch(Exception e){
       System.out.println(e);
    }

It’s easy to use the result of executing an SPL script by a Java application. You can also load the SPL script directly and use its name as a parameter to execute it via a function. esProc supports ODBC, too. The integration via ODBC is similar.

MongoDB’s schemaless document-based structure enables storage convenient and flexible data presentation and query. On the other hand, it increases the complexity of a shell script because there are details to consider and it needs many steps to get to the goal. By contrast, SPL streamlines the process, generates concise and simple code, and increases efficiency.