* 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.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL