* How to Combine Fields of Same Attribute in MongoDB
As a document-based database, MongoDB allows storing data in a convenient and flexible way. Data of same or similar attribute can be stored in different formats. users collection, for instance, records telephones for user July and user Tom in different ways, in multiple fields and as an array respectively. We want to group the documents by NAME and combine TEL under each name. Below is the source data:
…… |
{ |
Here’s the MongoDB way of getting this done. It gets field names in users collection and then the corresponding TEL fields by field name matching, and use mapreduce to combine the TEL fields. The process is roundabout.
An easy alternative is esProc SPL. With SPL script, we can first locate TEL-related fields and then combine values under them. It’s more direct and convenient.
Download esProc installation package HERE.
Directions:
1. Write SPL script tels.dfx in esProc:
A |
B |
|
1 |
=mongo_open("mongodb://localhost:27017/local") |
/ Connect to MongoDB database |
2 |
=mongo_shell(A1,"users.find(,{_id:0})") |
/ Get data of users collection |
3 |
=A2.fname().pselect@a(like(~, "TEL*")) |
/ Get the column number commanding fields headed by TEL |
4 |
=A2.new(username, gender, age, ( t=~.array(), A3.conj(t(~))\[null] ):tels) |
/ Combine telephone numbers, remove empty values and stores the result in TELS fields of the target new table sequence |
5 |
>A1.close() |
/ Close database connection |
2. Debug and execute the script. Below is A3’s value:
A3 |
Member |
5 |
|
6 |
|
…… |
3. Execute the script to return the final result:
A4 |
username |
gender |
age |
tels |
July |
F |
17 |
[18811223298,185814…] |
|
Tom |
M |
15 |
[13800226298,185114…] |
|
… |
A4: Store desired records in variable t, get telephone numbers by the sequence number of columns containing TEL and combine them.
esProc supplies JDBC interface to let you integrate the script into a Java program easily:
public static void doWork() {
Connection con = null;
java.sql.Statement st;
try{
Class.forName("com.esproc.jdbc.InternalDriver");
con = DriverManager.getConnection("jdbc:esproc:local://");
// Call script tels.dfx
st=con.createStatement();
ResultSet rst = st.executeQuery("call tels");
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);
}
}
}
}
Data of a same attribute could be records in same or different fields. You can always use esProc to handle them.
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/esProcSPL
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