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

 ……
    {
  "_id":ObjectId("5fd305b4db8950e48f0cf196"),
    "username":"July",
    "gender":"F",
    "age":17,
    "TEL1":"18811223298",
    "TEL2":"18581418158",
    "TEL3":"15286856439"
  }

{
  "_id":ObjectId("5fd30828db8950e48f0cf197"),
    "username":"Tom",
    "gender":"M",
    "age":15,
    "TEL":[
      "13800226298",
      "18511418100",
      "13986666413"
    ]
  }
  ……

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. With esProc, we can first locate TEL-related fields and then combine values under them. It’s more direct and convenient.
Download esProc installation package and free license file
HERE.

Directions:
1. Write 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 script into a Java program.