* Perform Associated Query through Array Field on MongoDB Collections


Get record from a table according to the associated array field in another table and generate a new collection made up of specific fields.
Here are two collections users and workouts. They are associated through array field users.WorkId and workouts._id.

users

_id

Name

WorkId

1000

xxx

[2,4,6]

1002

yyy

[1,3,5]

 

workouts

_id

Date

Book

1

1/1/2001

Othello

2

2/2/2001

A  Midsummer Night's Dream

3

3/3/2001

The Old  Man and the Sea

4

4/4/2001

GULLIVER'S  TRAVELS

5

5/5/2001

Pickwick  Papers

6

6/6/2001

The Red  and the Black

7

7/7/2001

Running

The expected query result:

Name

_id

Date

Book

xxx

2

2/2/2001

A  Midsummer Night's Dream

xxx

4

4/4/2001

GULLIVER’S  TRAVELS

xxx

6

6/6/2001

The Red  and the Black

yyy

1

1/1/2001

Othello

yyy

3

3/3/2001

The Old  Man and the Sea

yyy

5

5/5/2001

Pickwick  Papers

The MongoDB way of doing this is to join the two collections using $lookup, use unwind to split users.workouts table into records and lift them to the level of NAME, and then display the desired fields. The process is really a hassle.

esProc SPL handles this by preceding the name to the workouts records whose id field values are included in users.WorkId. That’s much easier.

Download esProc installation package HERE.
Directions to implement the esProc algorithm:

1. Write SPL script workout.dfx in esProc:

A

B

1

=mongo_open("mongodb://127.0.0.1:27017/raqdb")

/ Connect to MongoDB database

2

=mongo_shell(A1,"users.find()").fetch()

/ Get data from users collection

3

=mongo_shell(A1,"workouts.find()").fetch()

/ Get data from workouts collection

4

=A2.conj(A3.select(A2.WorkId.pos(_id)).derive(A2.name:Name))

/ Get records of A3’s table   sequence whose _id values are included in WorkId array of A2’s table sequence,   add name field to them and return the joined table sequence

5

>mongo_close(A1)

/ Close database connection

2. Execute the script and return the final result:

_id

Date

Book

Name

2

2/2/2001

A  Midsummer Night's Dream

xxx

4

4/4/2001

GULLIVER’S  TRAVELS

xxx

6

6/6/2001

The Red  and the Black

xxx

1

1/1/2001

Othello

yyy

3

3/3/2001

The Old  Man and the Sea

yyy

5

5/5/2001

Pickwick  Papers

yyy

A4: A3.select() gets records of workouts whose _id values are included in users.WorkId and add the name field required in the result set to workouts.  

esProc provides JDBC interface, so you can easily integrate the script into a Java program:
public
static void doWorkout() {
  Connection con = null;
  java.sql.Statement st;
 
  try{
    Class.forName("com.esproc.jdbc.InternalDriver");
    con = DriverManager.getConnection("jdbc:esproc:local://");
st=con.createStatement();
// Call script workout.dfx
ResultSet rst = st.executeQuery("call workout");
    System.out.println(rst);
   }catch(Exception e){
   System.out.println(e);
   }finally{
    // Close connection
    if (con!=null) {
      try {
       con.close();
     }catch(Exception e) {
        System.out.println(e);
      }
     }
   }
}

Read How to Call an SPL Script in Java to learn more about integration of esProc SPL script into a Java program.