* Concatenate and Sum MongoDB Documents with Different Fields

MongoDB stores data as documents in the format of bson. Each record is a document; documents have their own keys and number of keys. It’s not easy to group and summarize data stored in such a nested, document-based structure. Here’s a MongoDB collection:
{"name": "test","num": {"text": 1,"face": 2}},
{"name": "test","num": {"image": 3,"face": 4}},
{"name": "test","num": {"text":5,"image": 6, "book": 12}},
{"name": "demo","num": {"text": 3,"face": 4}},
{"name": "demo","num": {"image": 7,"face": 8}},
{"name": "demo","num": {"text":5,"image": 5}}
According to this collection, we want to find the quantity of each product. Below is the expected query result:
{"_id" : "test", "count" : { "image" : 9, "book" : 12, "face" : 6, "text" : 6} }
{"_id" : "demo", "count" : { "image" : 12, "text" : 8, "face" : 12} }

According to the MongoDB scripting way, it will split each num array value to convert them to a document for aggregation, and then group and filtering the result to display. That’s complicated.

It would be convenient if you could use esProc SPL to rearrange the MongoDB data into a table sequence, perform grouping and sum over it and then row-to-column transposition.
Download esProc installation package
HERE.

Directions to implement the esProc algorithm:
1. Write SPL script test.dfx in esProc:

A

B

1

=mongo_open("mongodb://localhost:27017/cookie  ")

/ Connect to database

2

=mongo_shell(A1,"test.find(   {},  {_id: 0})").fetch()

/ Query set test

3

=A2.(~.(( d=num.array(),  num.fname().( name|~| d(#)) )).conj())

/ Correspond fields of num to   their values

4

=create(NAME, SNAME,  NUM).record(A3.conj())

/ Store A3’s records into a   new table sequence

5

=A4.groups(NAME, SNAME;   sum(NUM):  COUNT)

/ Group records by NAME and   SNAME and then sum num

6

=A5.pivot(NAME;SNAME,COUNT)

/ Transpose SNAME and COUNT   to fields

7

>mongo_close(A1)

/ Close database connection


2. Debug and execute the script, and then check A3’s value:

A3

Member

[test,text,1.0,…]

[test,image,3.0,…]


> Values of other cells won’t be listed here.
3. Execute the script and return the final result:

A6

NAME

book

face

image

demo

(null)

12.0

12.0

test

12.0

6.0

9.0

A3 matches each field name under NUM to their values and then form a record with NAME. A4 populates A4’s records to the table sequence, groups the table and performs sum, and then transposition.

esProc provides JDBC interface, so you can easily integrate the script into a Java program:

public
static void doTest() {
   Connection con = null;
   java.sql.Statement st;
 
  try{
    Class.forName("com.esproc.jdbc.InternalDriver");
    con = DriverManager.getConnection("jdbc:esproc:local://");
// Call script test.dfx
    st=con.createStatement();
ResultSet rst = st.executeQuery("call test");
     System.out.println(rst);
   }catch(Exception e){
    System.out.println(e);
   }finally{
     // Close database connection
    if (con!=null) {
      try {
     con.close();
      }catch(Exception e) {
        System.out.println(e);
       }
     }
   }
}

Read How to Call an SPL Script in Java to learn more about integration of esProc SPL script into a Java program.