esProc Heterogeneous Datasources – MongoDB

It is convenient to establish and close a connection to MongoDB in esProc , as well as to call the database to query and count the data, perform distinct and aggregate operations.

1. Preparation for connecting to MongoDB

The MongoDB Java driver (like mongo-java-driver-2.12.2.jar), which esProc does’nt provide, should be put into [esProc installation directory]\common\jdbc beforehand when esProc designer is used to access the database. The MongoDB Java driver’s download link is https://github.com/mongodb/mongo-java-driver/releases.

To access MongoDB by calling the esProc script through esProc JDBC in a Java program, the above MongoDB Java driver needs to be put into the classpath of the Java program.

2. Create and close a connection to MongoDB

In an esProc script, mongodb(con) function is used to establish a connection to the MongoDB database and mdb.close() function is used to close it, as shown by the following code:

A1: Connect to MongoDB. IP and port are localhost:27017; database name is test, and so are the user name and the password.

A2: Close the MongoDB connection.

3. Common data query and selection

Let’s take the employee, a collection in the database whose name is test, of MongoDB as an example to illustrate the esProc script for accessing MongoDB. Some of the data of employee are as follows:

{ “_id” : ObjectId(“546aea22ee62ea452bdef4fe”), “EID” : 1, “NAME” : “Rebecca”, ”

SURNAME” : “Moore”, “GENDER” : “F”, “STATE” : “California”, “BIRTHDAY” : “1974-1

1-20”, “HIREDATE” : “2005-03-11”, “DEPT” : “R&D”, “SALARY” : 7000 }

{ “_id” : ObjectId(“546aea22ee62ea452bdef4ff”), “EID” : 2, “NAME” : “Ashley”, “S

URNAME” : “Wilson”, “GENDER” : “F”, “STATE” : “New York”, “BIRTHDAY” : “1980-07-

19”, “HIREDATE” : “2008-03-16”, “DEPT” : “Finance”, “SALARY” : 11000 }

{ “_id” : ObjectId(“546aea22ee62ea452bdef500”), “EID” : 3, “NAME” : “Rachel”, “S

URNAME” : “Johnson”, “GENDER” : “F”, “STATE” : “New Mexico”, “BIRTHDAY” : “1970-

12-17”, “HIREDATE” : “2010-12-01”, “DEPT” : “Sales”, “SALARY” : 9000 }

…

The following is a sample of esProc script for querying data of the collection of MongoDB:

A2: Filter and query the data of employee. find function’s first parameter is employee, the collection’s name; its second parameter is the filtering criterion: STATE=” California” and SALARY >4,000; the third one specifies which properties should be fetched (here GENDER won’t be fetched). Noticed that esProc uses the same parameter format as that used in MongoDB’s find statement.

The function returns a cursor, which can fetch data in batch for computing when the result set is big, so as to avoid the memory overflow. The usage of cursor has been explained in detail in esProc-related documents. Here suppose the data are not big and use fetch function to fetch all the results for observation:

4. Query and filter nested arrays and documents

Nested arrays and docements are multilevel data structure specific to MongoDB. The Java application doesn’t support this multilevel data structure when it accesses MongoDB through JDBC. Because esProc supports referencing data by fields, like referencing a record sequence by a field, it can fetch the nested arrays and documents of MongoDB.

For a nested array, let’s look at a collection – fruitshop:

{ “_id” : ObjectId(“548a58c19cfbc9dd702eb71e”), “name” : “big fruit”, “fruits” :

 [ “apple”, “pear”, “orange” ] }

{ “_id” : ObjectId(“548a58c19cfbc9dd702eb71f”), “name” : “good fruit”, “fruits”

: [ “banana”, “pear”, “orange” ] }

{ “_id” : ObjectId(“548a58c29cfbc9dd702eb720”), “name” : “my fruit”, “fruits” :

[ “banana”, “apple”, “tomato” ] }

And for a nested docment, let’s look at this collection – blogs:

{ “_id” : ObjectId(“548a59fa9cfbc9dd702eb721”), “content” : “It is too hot”, “co

mment” : [ { “author” : “joe”, “score” : 3, “comment” : “just so so!” }, { “auth

or” : “jimmy”, “score” : 5, “comment” : “cool! good!” } ] }

{ “_id” : ObjectId(“548a59fa9cfbc9dd702eb722”), “content” : “It is too cold”, “c

omment” : [ { “author” : “james”, “score” : 1, “comment” : “yes!” }, { “author”

: “jimmy”, “score” : 5, “comment” : “cool!” } ] }

{ “_id” : ObjectId(“548a59fb9cfbc9dd702eb723”), “content” : “It is windy day tod

ay”, “comment” : [ { “author” : “tom”, “score” : 3, “comment” : “I do not think

so!” }, { “author” : “jimmy”, “score” : 5, “comment” : “cool!” } ] }

esProc script for querying and filtering data of the two collections:

A2: The filtering criterion of find function includes the pear insead of being equal to pear, for the fruits is an array. The return result of find funciton is a cursor, from which the resulting table sequence is fetched using fetch function and you can see fruits field is a referencing field, which references a sequence consisiting of various fruits names, as shown below:

Click the blue part of the first row to see the detailed information of the referenced sequence, as shown below:

Based on this table sequence with a referencing field, esProc can further perform more complicated set operations.

A3: comment is a collection of documents, and the filtering criterion is the comment’s documents whose author is jimmy. find function returns a cursor, from which the table sequence is fetched using fetch function. comment field of the table sequence is a referencing field that references a table sequence, as shown below:

Click the blue part of the second row to see the detailed information of the table sequence, as shown below:

Based on this table sequence with a referencing field, esProc can further perform more complicated set operations. Here further discussion is omitted.  

5. Count the data, perfor distinct and aggregate operations

With more powerful ability in computing and flow control, usually esProc is used to perform the complicated multi-step operation and MongoDB, a highly-efficient storage solution, is responsible for providing data without participating in the computation. But considering the performance loss during the data transmission, you can use MongoDB alone to perform some simple aggregate operations.

esProc provides the way of calling MongoDB interface to count the data, perform distinct and aggregate operations. Different from the find function for querying and filtering data, the return results of the three operations are table sequences instead of cursors. A sample code is as follows:

A2: Count the number of eligible files in employee, a collection, using count function. The function’s first parameter is employee, the collection’s name; its second parameter is the filtering criterion: STATE=” California”. The result is count value:

A3: Get the distinct values from employee using distinct function. The function’s first parameter is employee, the collection’s name, and the second parameter is one of the properties, DEPT. The return result is a table sequence consisting of employee’s DEPT without duplicate members:

A4: Group and summarize employee. The first parameter of aggregate function is employee, the collection’s name, and the second one includes the grouping property and aggregate property, which means the data of the collection will be grouped by DEPT and then count the number of files in each group. aggregate function returns a cursor as well, whose data will be fetched all at once using fetch function.