* 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:

{
   "_id":   ObjectId("99014006e4b0aaac99a104ae"),
   "access" : {
       "append" : { "uids" :[
      ObjectId("94f9bfb0aa6a19084789ca9a")],
        "level" : [1] },
     "edit" : {"level"   : [3],
        "uids"  : [
      ObjectId("94f9bfb0aa6a19084789ca92")]},
     "manager" :   {"uids"  : [],   "level" : [4, 7] },
     "read" : {   "uids" : [
        ObjectId("94f9bfb0aa6a19084789ca92"),
             ObjectId("94f9bfb0aa6a19084789ca98")],
            "level" :   [3, 8] }
      },
      "name" : "article"
  }

{
  "_id":
      ObjectId("99014006e4b0aaac99a104af"),
    "access":{
       "append" : { "uids" :[
          ObjectId("94f9bfb0aa6a19084789ca69")],
          "level" : [4] },
       "edit" : {"level" : [5],
             "uids"     : [
            ObjectId("94f9bfb0aa6a19084789ca92")]},
          "manager" : { "uids" : [ ], "level" : [1] },
        "read" : { "uids" : [
          ObjectId("94f9bfb0aa6a19084789ca92"),
            ObjectId("94f9bfb0aa6a19084789ca70")],
        "level" : [3 ] }
      },
      "name" : "article"    }

  …….

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.