MongoDB is a schemaless, document-based database. It’s hard for it to play a role in handling multitable joins that relational databases are remarkably good at. Though later MongoDB versions provide $lookup function to be able to deal with single-field joins, they are barely able to manage multi-field joins. To join collection C1 and collection C2 through C1.sid=C2.sid and C1.mid=C2.mid, for instance:

The expected query result:

The MongoDB way of doing this is to join the two collection using $lookup function and return a nested structure, split the nested structure into documents using unwind function, filter documents through the combination of redact+cond+$$KEEP+$$PRUNE, and finally display the desired fields. That’s rather complicated.

The process would become simple and convenient if you could use esProc SPL to do the join through C1.sid=C2.sid and C1.mid=C2.mid.
Download esProc installation package HERE.

Directions for accomplishing the task with esProc:
1. Write SPL script cc.dfx in esProc:

2. Execute the script and return the following result:

A4: The sid and mid in A2.join() are A2’s fields corresponding to A3’s counterparts.
esProc offers JDBC interface, so you can easily integrate the script into a Java program:

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 cc.dfx
    st=con.createStatement();
    ResultSet rst = st.executeQuery("call cc");
    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);
       }
     }
   }
}

esProc can deal with both multifield join between two collections and a join between multiple collections.
Read How to Call an SPL Script in Java to learn more about integration of esProc SPL script into a Java program.