* How to Combine Subdocuments of Different Attributes in MongoDB
MongoDB can record multiple attributes and provide moredetails of a subdocument through nested structure. Below is blog collection. There are append, edit, manager and read fields under its subdocument access. We want to group documents by NAME and combine uids fields under it while removing duplicates at the same time. Below is the source data:
{ |
{ ……. |
In MogoDB, we can use $addFields to convert objects into KV arrays, split these arrays using $unwind, group document by NAME and combine subdocuments, and re-split and group until uids are combined. The process is extremely complicated.
A simple alternative is to group documents by name, combine uids values and perform distinct in each group. It’s much easier to implement.
Download esProc installation package HERE.
Directions:
1. Write SPL script blog.dfx in esProc:
A |
B |
|
1 |
=mongo_open("mongodb://localhost:27017/local") |
/ Connect to MongoDB database |
2 |
=mongo_shell(A1,"blog.find(,{_id:0};{name:1})") |
/ Get data of blog collection |
3 |
=A2.group(name; ~.conj(access.read.uids|access.append.uids| access.edit.uids|access.manager.uids).id(): uids) |
/ Group by name and combine uids values under all subdocuments and remove duplicates |
4 |
>A1.close() |
/ Close database connection |
2. Execute the script to return the final result:
A3 |
name |
uids |
article |
[94f9bfb0aa6a19084789ca69,94f9bfb0a…] |
|
story |
[94f9bfb0aa6a19084789ca60,94f9bfb0a…] |
|
… |
A3: Group documents by name, combine uids under all subdocuments and remove duplicates in each group
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 blog.dfx
st=con.createStatement();
ResultSet rst = st.executeQuery("call blog");
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);
}
}
}
}
You can always combine values under attributes in different nested levels with esProc.
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