esProc Helps with Computation in MongoDB – Cross Summarizing

It is difficult for MongoDB to realize the operation of cross summarizing. It is also quite complicated to realize it using high-level languages, like Java, after the desired data is retrieved out. In this case, you can consider using esProc to help MongoDB realize the operation. The following example will teach you how it works in detail.

A collection – student – is given in the following:

db.student.insert  ( {school:'school1', sname : 'Sean' , sub1: 4, sub2 :5 })

db.student.insert  ( {school:'school1', sname : 'chris' , sub1: 4, sub2 :3 })

db.student.insert  ( {school:'school1', sname : 'becky' , sub1: 5, sub2 :4 })

db.student.insert  ( {school:'school1', sname : 'sam' , sub1: 5, sub2 :4 })

db.student.insert  ( {school:'school2', sname : 'dustin' , sub1: 2, sub2 :2 })

db.student.insert  ( {school:'school2', sname : 'greg' , sub1: 3, sub2 :4 })

db.student.insert  ( {school:'school2', sname : 'peter' , sub1: 5, sub2 :1 })

db.student.insert  ( {school:'school2', sname : 'brad' , sub1: 2, sub2 :2 })

db.student.insert  ( {school:'school2', sname : 'liz' , sub1: 3, sub2 :null })

We are expected to produce a cross table as the one in the following, in which each row is a school and the first column holds students whose results of sub1 are a 5 and the second column holds those whose results of sub1 are a 4 and so forth.

esProc script:

A1: Connect to MongoDB. Both IP and the port number are localhost:27017. The database name, user name and the password all are test.

A2: Use find function to fetch the collection – student - from MongoDB and create a cursor. Here esProc uses the same parameter format in find function as that in find statement of MongoDB. As esProc's cursor supports fetching and processing data in batches, the memory overflow caused by importing big data all at once can thus be avoided. In this case, the data can be fetched altogether using fetch function because the size is not big.

A3: Group the data by schools.

A4: Then group each group of data in alignment according to the sequence [1,2,3,4,5] and compute the length of each subgroup.

A5: Put the lengths got in A4 into corresponding positions as required and a record sequence wil be generated as the result.

The result is as follows:

Note：esProc isn't equipped with a Java driver included in MongoDB. So to access MongoDB using esProc, you must put MongoDB's Java driver (a version of 2.12.2 or above is required for esProc, e.g. mongo-java-driver-2.12.2.jar) into [esProc installation directory]\common\jdbc beforehand.

The esProc script used to help MongoDB with the computation is easy to be integrated into the Java program. You just need to add another line of code - result A6 to output a result in the form of resultset to Java program. For the detailed code, please refer to esProc Tutorial. In the same way, MongoDB's Java driver must be put into the classpath of a Java program before the latter accesses MongoDB by calling an esProc program. 

esProc Simplifies SQL-style Computations – In-group Computation

During developing the database applications, we often need to perform computations on the grouped data in each group. For example, list the names of the students who have published papers in each of the past three years; make statistics of the employees who have taken part in all previous training; select the top three days when each client gets the highest scores in a golf game; and the like. To perform these computations, SQL needs multi-layered nests, which will make the code difficult to understand and maintain. By contrast, esProc is better at handling this kind of in-group computation, as well as easy to integrate with Java and the reporting tool. We’ll illustrate this through an example.

According to the database table SaleData, select the clients whose sales amount of each month in the year 2013 is always in the top 20. Part of the data of SalesData is as follows:

To complete the task, first select the sales data of the year of 2013, and then group the data by the month and, in each group, select the clients whose monthly sales amount is in the top 20. Finally, compute the intersection of these groups.

With esProc we can split this complicated problem into several steps and then get the final result. First, retrieve the data of 2013 from SaleData and group it by the month:

Note: The code for filtering in A2 can also be written in SQL.

It is the real grouping that esProc separates data into multiple groups. This is different from the case in SQL, whose group by command will compute the summary value of each group directly and won't keep the intermediate results of the grouping. After grouping, the data in A3 are as follows:

esProc will sorts the data automatically before grouping. Each group is a set of sales data. The data of March, for example, are as follows:

In order to compute every client's sales amount of each month, we need to group the data a second time by clients. In esProc, we just need to perform this step by looping the data of each month and group it respectively. A.(x) can be used to execute the loop on members of a certain group, with no necessity for loop code.

A4：=A3.(~group(Client))

In A4, the data of each month constitute a subgroup of each previous group after the second grouping:

At this point, the data of March are as follows:

It can be seen that each group of data in March contains the sales data of a certain client. 

Please note "~" in the above code represents each member of the group, and the code written with "~" is called in-group computation code, like the above-mentioned ~.group(Client).

Next, select the clients whose rankings of each month are in the top 20 through the in-group computation:

A5：=A4.(~.top(-sum(Amount);20))

A6：=A5.(~.new(Client,sum(Amount):MonthAmount))

A5 computes the top 20 clients of each month in sales amount by looping each month's data. A6 lists the clients and their sales amount every month. The result of A6 is as follows:

Finally, list the field Client of each subgroup and compute the intersection of the subgroups:

A7：=A6.(~.(Client))

A8：=A7.isect()

A7 computes the top 20 clients of each month in sales amount. A8 computes the intersection of the field Clients of the twelve months. The result is as follows:

As can be seen from this problem, esProc can easily realize the in-group computation, including the second group and sort, on the structured data, make the solving way more visually, and display a clear and smooth data processing in each step. Moreover, the operations, like looping members of a group or computing intersection, become easier in esProc, which will reduce the amount of code significantly.

The method with which a Java program calls esProc is similar to that with which it calls an ordinary database. The JDBC provided by esProc can be used to return a computed result of the form of ResultSet to Java main program. For more details, please refer to the related documents .

esProc Simplifies SQL-style Computations – Records Corresponding to Max Value

`In developing database applications, usually it is the records corresponding to the max/min value that we need to retrieve, instead of the value itself. For example, the occasion in which each employee gets his/her biggest pay raise; the three lowest scores ever got in golf; the five days in each month when each product gets its highest sales amount; and so on. As the max function of SQL can only retrieve the max value, instead of the records to which the max value corresponds, it is quite complicated to handle the computation in SQL with the aid of some advanced techniques, such as window functions or the nested sub-queries or keep/top/row number. If multi-layered grouping or relations are involved, the computation will become even more complicated.

With the top function in esProc, the records corresponding to the max value can be retrieved and the computation becomes much easier. The following is such an example.

The database table golf contains the scores of members in a golf club. Please select the best three scores each member has ever got. Part of the data is as follows:

The code written in esProc:

A1: Retrieve data from the database. If the data come from a structured text file, the following equivalent code can be used: =file("\\golf").import@t(). Click the cell and we can check the retrieving result:

A2: =A1.group(User_ID), i.e., group the result of A1. The result is as follows:

As shown in the above figure, the data have been separated into multiple groups by User_ID and each row is a group. Click the blue hyperlink and members of the group will be shown as follows:

A3: =A2.(~.top(-Score;3)). The code is to compute the records of each group of data whose field Score is in the top three. Here "~" represents each group of data. ~.top() represents that top function will work on every group of data in turn. The top function can retrieve the N biggest/smallest records from a data set. For example, top(Score;3) represents sorting by Score in ascending order and fetching the first three records (i.e. min values); top(-Score;3) represents sorting in descending order and fetching the first three records (i.e. max values). The result of this step is as follows:

A4: =A3.union(), which means union data of every group. The result is as follows: 

In the above, the computation is performed step by step. But the steps can be integrated into one for the convenience of maintenance and debugging: db.query("select * from golf").group(User_ID). (~.top(-Score;3)).union().

The method with which a Java program calls esProc is similar to that with which it calls an ordinary database. The JDBC provided by esProc can be used to return a computed result of the form of ResultSet to Java main program. For more details, please refer to the related documents.

esProc Helps Process Heterogeneous Data Sources in Java –Cross-Database Relating

JoinRowSet and FilteredRowSet provided by RowSet– Java's class library for data computing – can perform cross-database related computing, but they have a lot of weaknesses. First, JoinRowSet only supports inner join, it doesn't support outer join. Second, test shows that db2, mysql and hsql can work with JoinRowSet, yet the result set of join oracle11g to other databases is empty though no error reporting will appear. The fact is there were two users who perform cross-database join using oracle11g database even got the correct result. This suggests that JDBC produced by different database providers will probably affect the result obtained by using this method. Last, the code is complicated.

esProc has proved its ability in assisting Java to perform cross-database relating. It can work with various databases, such as oracle, db2, mysql, sqlserver, sybase and postgresql, to perform a variety of cross-database related computing, like inner join and outer join involving heterogeneous data. An example will teach you the way esProc works. Requirement: relate table sales in db2 to table employee in mysql through sale.sellerid and employee.eid, and then filter data in both sales and employee according to the criterion state="California". The way the code is written in this task applies to situations where other types of databases are involved.

The structure and data of table sales are as follows: 

The structure and data of table employee are as follows:

Implementation approach: Call esProc script using Java program, join the multiple databases together to realize the cross-database relating, perform filtering and return the result to Java in the form of ResultSet.

The code written in esProc is as follows:

A1：Connect to the data source db2 configured in advance.

A2：Connect to the data source mysql configured in advance. In fact oracle and other types of databases can be used too.

A3, A4：Retrieve table sequences: sales and employee, from db2 and mysql respectively. esProc's Integration Development Environment (IDE) can display the retrieved data visually, as shown in the right part of the figure in the above.

A5：Relate sales to employee through sellerid=eid using esProc's object reference mechanism.

A6：Filter the two table sequences according to state="California".

A7：Generate a new table sequence and get the desired fields.

A8：Return the result to the caller of esProc program.

This piece of program is called in Java using esProc JDBC to get the result. The code is as follows (save the above esProc program as test.dfx):

//create a connection using esProcjdbc

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://");

// call esProc program (the stored procedure) in which test is the name of file dfx

com.esproc.jdbc.InternalCStatementst;

st =(com.esproc.jdbc.InternalCStatement)con.prepareCall("call test()");

// execute esProc stored procedure

st.execute();

// get the result set

ResultSet set = st.getResultSet();

esProc Helps with Computation in MongoDB – Relationships between Tables

MongoDB doesn’t support join. The unity JDBC recommended by its official website can perform join operation after retrieving data out, but charges a fee for the service. Other free JDBC drivers can only support the basic SQL statements, without join included. If you use programming languages, like Java, to retrieve data first and proceed to perform join operation, the process will still be complicated.

However, the join operation can be realized by using esProc, which is free of charge, to help MongoDB with the computation. Here is an example for illustrating the method in detail.

In MongoDB, there is a document - orders – that holds the order data, and another file – employee – for storing employee data, as shown in the following:

MongoDB shell version: 2.6.4

connecting to: test

> db.orders.find();

{ "_id" : ObjectId("5434f88dd00ab5276493e270"), "ORDERID" : 1, "CLIENT" : "UJRNP

", "SELLERID" : 17, "AMOUNT" : 392, "ORDERDATE" : "2008/11/2 15:28" }

{ "_id" : ObjectId("5434f88dd00ab5276493e271"), "ORDERID" : 2, "CLIENT" : "SJCH"

, "SELLERID" : 6, "AMOUNT" : 4802, "ORDERDATE" : "2008/11/9 15:28" }

{ "_id" : ObjectId("5434f88dd00ab5276493e272"), "ORDERID" : 3, "CLIENT" : "UJRNP

", "SELLERID" : 16, "AMOUNT" : 13500, "ORDERDATE" : "2008/11/5 15:28" }

{ "_id" : ObjectId("5434f88dd00ab5276493e273"), "ORDERID" : 4, "CLIENT" : "PWQ",

 "SELLERID" : 9, "AMOUNT" : 26100, "ORDERDATE" : "2008/11/8 15:28" }

…

> db.employee.find();

{ "_id" : ObjectId("5437413513bdf2a4048f3480"), "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("5437413513bdf2a4048f3481"), "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("5437413513bdf2a4048f3482"), "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 SELLERID in orders corresponds to EID in employee. Please select from employee all order information in which the state is California. orders holds big data and thus cannot be loaded into the memory entirely. But employee has fewer data and so has the filtering result of orders.

The conditional expression for selecting data can be passed to the esProc program as a parameter, as shown in the following figure:

esProc code:

A1: Connect to MongoDB. Both IP and the port number are localhost:27017. The database name, user name and the password all are test.

A2: find function is used to fetch data from MongoDB and create a cursor. orders is the collection, with a filtering condition being null and _id , the specified key, not being fetched. esProc uses the same parameter format in find function as that in find statement of MongoDB. esProc's cursor supports fetching and processing data in batches, thereby avoiding the memory overflow caused by importing big data at once.

A3: Fetch data from employee. Because the data size to be fetched is not big, you can use fetch function to get them all at once.

A4: switch function is used to convert values of SELLERID field in A2's cursor into the record references in A3's employee. 

A5: Select the desired data according to the condition. Here a macro is used to dynamically parse an expression, in which where is the input parameter. In esProc, the expression surrounded by ${…} will be first computed, the result be taken as the macro string value to replace ${…} , and then the code be interpreted and executed. Therefore the actual code executed in this step is =A4.select(SELLERID.STATE=="California"). Since SELLERID has been converted into the references of corresponding records in employee, you can write the code in such a way as SELLERID.STATE. As the data size of the filtering result is not big, you can fetch the data all at once. But if the data size is still rather big, you can fetch the data in batches, say, 10,000 rows per batch, which can be expressed as fetch(10000).

A6: Reconvert the values of SELLERID in the filtering result into ordinary ones. 

The result of A6 is as follows:

When the filtering condition is changed, you need not change the whole program, but modifying the parameter where. For example, the condition is changed to "orders information in which state is California or CLIENT is PWQ", then the value of where can be expressed as CLIENT=="PWQ"|| SELLERID.STATE=="California".

The thing is that esProc isn't equipped with a Java driver included in MongoDB. So to access MongoDB using esProc, you must put MongoDB's Java driver (a version of 2.12.2 or above is required for esProc, e.g. mongo-java-driver-2.12.2.jar) into [esProc installation directory]\common\jdbc beforehand.

The script written in esProc which is used to help MongoDB with the computation is easy to be integrated into the Java program. You just need to add another line of code - A7 – that is, result A6, for outputting a result in the form of resultset to Java program. For the detailed code, please refer to esProc Tutorial. In the same way, MongoDB's Java driver must be put into the classpath of a Java program before the latter accesses MongoDB by calling an esProc program.  

esProc Helps Process Heterogeneous DataSources in Java - MongoDB

MongoDB doesn't support join directly. The unity JDBC recommended by the official website can perform the join operation after retrieving data out. But the advanced functions, like join, group, functions and expressions, are only provided by the paid version of unity JDBC. Even the paid version doesn't support the complicated SQL operations, such as subquery, window functions, etc. The free JDBC drivers can only support the most basic SQL statements.

Using free esProc working with MongoDB can realize the above-mentioned complicated structured (or semi-structured) computations. We'll take join as an example to illustrate the method in detail.

As shown in the following, the file -orders- in MongoDB contains the sales orders, and employee contains the employee information:

MongoDB shell version: 2.6.4

connecting to: test

>db.orders.find();

{ "_id" : ObjectId("5434f88dd00ab5276493e270"), "ORDERID" : 1, "CLIENT" : "UJRNP

", "SELLERID" : 17, "AMOUNT" : 392, "ORDERDATE" : "2008/11/2 15:28" }

{ "_id" : ObjectId("5434f88dd00ab5276493e271"), "ORDERID" : 2, "CLIENT" : "SJCH"

, "SELLERID" : 6, "AMOUNT" : 4802, "ORDERDATE" : "2008/11/9 15:28" }

{ "_id" : ObjectId("5434f88dd00ab5276493e272"), "ORDERID" : 3, "CLIENT" : "UJRNP

", "SELLERID" : 16, "AMOUNT" : 13500, "ORDERDATE" : "2008/11/5 15:28" }

{ "_id" : ObjectId("5434f88dd00ab5276493e273"), "ORDERID" : 4, "CLIENT" : "PWQ",

 "SELLERID" : 9, "AMOUNT" : 26100, "ORDERDATE" : "2008/11/8 15:28" }

…

>db.employee.find();

{ "_id" : ObjectId("5437413513bdf2a4048f3480"), "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("5437413513bdf2a4048f3481"), "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("5437413513bdf2a4048f3482"), "EID" : 3, "NAME" : "Rachel", "S

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

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

…

SELLERID in orders corresponds to EID in employee. Query the information of sales orders that satisfies the criterion that the property of STATE of employee is California. The expression of querying condition can be passed to esProc program as a parameter, as shown below: 

As esProc isn't equipped with the java driver of MongoDB, the latter should be put into the [esProc installation directory]\common\jdbc in advance when esProc is used to access MongoDB (esProc requires a driver of 2.12.2 version or above, i.e. mongo-java-driver-2.12.2.jar).

        

The jar can be downloaded from the URL https://github.com/mongodb/mongo-java-driver/releases.

The code written in esProc is as follows:

A1: Connect to the MongoDB database. The IP and port number is localhost:27017, the name of the database is test and both the user name and the password are test. If any other parameters are needed, write them in line with the format mongo://ip:port/db?arg=value&…

A2: Fetch data from the MongoDB using find function to create a cursor. The set is orders. The filtering criterion is null and the specified key _id need not be fetched. It can be seen that this find function is similar to the find function of MongoDB. By fetching and processing data in batches, the esProc's cursor can avoid the memory overflow caused by big data importing. Since the data here are small, fetch function is used to fetch them at once.

A3: Fetch data from employee.

A4: switch function is used to switch the values of field SELLERID in A2 (orders) to the references of the corresponding records in A3 (employee).

A5: Filter the data according to the criterion. Here macro is used to realize dynamically parsing the expression, in which where is the input parameter. esProc will compute the expression surrounded by ${…} first, take the result as a macro string value and replace ${…} with it, and then interpret and execute the code. The code ultimately executed is =A2.select(SELLERID.STATE=="California"). Since the values of SELLERID have been switched to the references of corresponding records in employee, you can just write SELLERID.STATE.

A6: Switch back the values of SELLERID in the filtering result to ordinary values.

A7: Return the eligible result set to Java program.

If the filtering condition is changed, you just need to modify the parameter where without changing the program. For example, the condition is changed to "select the information of sales orders where STATE is California or that where CLIENT is PWQ". The value of the parameter where can be written as CLIENT=="PWQ"|| SELLERID.STATE=="California".

esProc JDBC is used to call this block of code in Java program and get the result (save the above esProc block of code as test.dfx and put the java driver of MongoDB into the classpath of Java program). The code is as follows:

// create a connection using esProc JDBC

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://");

// call the esProc program (stored procedure), in which test is the name of file dfx

com.esproc.jdbc.InternalCStatementst =(com.esproc.jdbc.InternalCStatement)con.prepareCall("call test(?)");

    // set the parameters

st.setObject(1,"CLIENT==\"PWQ\"||SELLERID.STATE==e\"California\"");        

// execute the esProc stored procedure

ResultSet set =st.executeQuery(); 

esProc Helps Process Heterogeneous Data Sources in Java - JSON

Java's JSON open source package can only parse JSON data and hasn't the computational function. It is troublesome for programmers to develop a general program for performing computations, such as grouping, sorting, filtering and joining, by themselves. For example, during developing program for performing conditional filtering in JSON files using Java, the code has to be modified if the conditional expression is changed. If they want to make it as flexible as SQL in performing conditional filtering, they have to write code for analyzing and evaluating expressions dynamically. This requires quite a lot of programming work.

esProc supports dynamic expressions. It also can be embedded in Java to write the general program for computing JSON data. Let's give an example to see how it works. There are to-be-processed JSON strings that contain employee information, including fields such as EID, NAME, SURNAME, GENDER, STATE, BIRTHDAY, HIREDATE and DEPT, etc. Parse the strings and select female employees who were born on and after January 1^st, 1981. The content of the strings is shown as follows:

[{EID:1,NAME:"Rebecca",SURNAME:"Moore",GENDER:"F",STATE:"California",BIRTHDAY:1974-11-20,HIREDATE:2005-03-11,DEPT:"R&D",SALARY:7000},

{EID:2,NAME:"Ashley",SURNAME:"Wilson",GENDER:"F",STATE:"New York",BIRTHDAY:1980-07-19,HIREDATE:2008-03-16,DEPT:"Finance",SALARY:11000},

{EID:3,NAME:"Rachel",SURNAME:"Johnson",GENDER:"F",STATE:"New Mexico",BIRTHDAY:1970-12-17,HIREDATE:2010-12-01,DEPT:"Sales",SALARY:9000},…]

Implementation approach: Call esProc program using Java and input the JSON strings which will then be parsed by esProc, perform the conditional filtering and return the result in JSON format to Java. Because esProc supports parsing and evaluating expression dynamically, it enables Java to filter JSON data as flexibly as SQL does.

For example, it is required to query female employees who were born on and after January 1, 1981. esProc can input two parameters: "jsonstr" and "where", as the conditions. This is shown as follows:

"where" is a string, its values is BIRTHDAY>=date(1981,1,1) && GENDER=="F".

The code written in esProc is as follows:

A1: Parse the JSON data into a table sequence. esProc's IDE can display the imported data visually, as shown in the right part of the above figure.

A2: Perform the conditional filtering, using macro to realize parsing the expression dynamically. The “where” in this process is an input parameter. In executing, esProc will first compute the expression surrounded by ${…}, take the computed result as macro string value, replace ${…} with it and then interpret and execute the code. The final code to be executed in this example is =A1.select(BIRTHDAY>=date(1981,1,1) && GENDER=="F").

A3: Generate JSON strings using the filtered table sequence.

A4: Return the eligible result set to the external program.

When the filtering condition is changed, you just need to modify "where"– the parameter. For example, it is required to query female employees who were born on and after January 1, 1981, or employees whose NAME+SURNAME is equal to "RebeccaMoore". The value of "where" can be written as BIRTHDAY>=date(1981,1,1) && GENDER=="F" || NAME+SURNAME=="RebeccaMoore". After the code is executed, the result set in A2 is as follows:

Since the esProc script is called in Java through the JDBC interface, the returned result is set - the object of ResultSet. Fetch the first field of string type in set, and this is the filtered JSON string. Detailed code is as follows (save the above program in esProc as test.dfx):

         // create a connection

         Class.forName("com.esproc.jdbc.InternalDriver");

         con= DriverManager.getConnection("jdbc:esproc:local://");

         // call the program in esProc (the stored procedure); test is the name of file dfx

         com.esproc.jdbc.InternalCStatementst;

         st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call json(?,?)");

         // set the parameters; as the JSON string is long, part of it is omitted.

// In practice, JSON strings may be generated by various ways; see below for the explanation.

          String jsonstr=

"[{EID:1,NAME:\"Rebecca\",SURNAME:\"Moore\",GENDER:\"F\",STATE:\"California\...}]";

         st.setObject(1,jsonstr);

         st.setObject(2,"BIRTHDAY>=date(1981,1,1) && GENDER==\"F\"");

// execute the esProcstored procedure

         ResultSet set=st.executeQuery();

         // get the filtered JSON string

         String jsonstrResult;

         if(set.next()) jsonstrResult = set.getString(1);

JSON format is common used by interactive data in internet application. In practice, JSON strings may be retrieved from local files or remote HTTP server. esProc can directly read JSON strings from the files or the HTTP server. Take the latter as an example. It is assumed that there is a testServlet which returns JSON strings of employee information. The code for performing the operation is as follows:

A1: Define an httpfile object, the URL is

         http://localhost:6080/myweb/servlet/testServlet?table=employee&type=json.

A2: Read the result returned by the httpfile object.

A3: Parse the JSON string and generate a table sequence.

A4: Filter data according to the conditions.

A5: Convert the filtered table sequence to JSON strings. 

A6: Return the result in A4 to the Java code that calls this piece of esProc program. 


More information:www.raqsoft.com.

esProc Helps with Computation in MongoDB – Sorting in Local Language

MongoDB uses unicode, instead of the coding for a certain local language, to sort data in this language (i.e. Chinese). Together with esProc, MongoDB can realize sorting in local language conveniently (i.e. sort Chinese according to Chinese phonetic alphabet). The following will teach you the method in detail by taking Chinese as an example.

person - a set in MongoDB - stores names and genders as follows:

> db.person.find()

{ "_id" : ObjectId("544e4e070f03ad39eb2bf498"), "name" : "宋江", "gender" : "男"}

{ "_id" : ObjectId("544e4e070f03ad39eb2bf499"), "name" : "李逵", "gender" : "男"}

{ "_id" : ObjectId("544e4e070f03ad39eb2bf49a"), "name" : "吴用", "gender" : "男"}

{ "_id" : ObjectId("544e4e070f03ad39eb2bf49b"), "name" : "晁盖", "gender" : "男"}

{ "_id" : ObjectId("544e4e070f03ad39eb2bf49c"), "name" : "公孙胜", "gender" : "男" }

{ "_id" : ObjectId("544e4e070f03ad39eb2bf49d"), "name" : "鲁智深", "gender" : "男" }

{ "_id" : ObjectId("544e4e070f03ad39eb2bf49e"), "name" : "武松", "gender" : "男"}

{ "_id" : ObjectId("544e4e070f03ad39eb2bf49f"), "name" : "阮小二", "gender" : "男" }

{ "_id" : ObjectId("544e4e070f03ad39eb2bf4a0"), "name" : "杨志", "gender" : "男"}

{ "_id" : ObjectId("544e4e070f03ad39eb2bf4a1"), "name" : "孙二娘", "gender" : "女" }

{ "_id" : ObjectId("544e4e070f03ad39eb2bf4a2"), "name" : "扈三娘", "gender" : "女" }

{ "_id" : ObjectId("544e4e080f03ad39eb2bf4a3"), "name" : "燕青", "gender" : "男"}

…

Sort the data using MongoDB's sort function rather than the Chinese phonetic alphabet:

> db.person.find({},{"name":1,"gender":1,"_id":0}).sort({"name":1})

{ "name" : "公孙胜", "gender" : "男" }

{ "name" : "吴用", "gender" : "男" }

{ "name" : "孙二娘", "gender" : "女" }

{ "name" : "宋江", "gender" : "男" }

{ "name" : "扈三娘", "gender" : "女" }

{ "name" : "晁盖", "gender" : "男" }

{ "name" : "李逵", "gender" : "男" }

{ "name" : "杨志", "gender" : "男" }

{ "name" : "武松", "gender" : "男" }

{ "name" : "燕青", "gender" : "男" }

{ "name" : "阮小二", "gender" : "男" }

{ "name" : "鲁智深", "gender" : "男" }

…

The esProc script helping with MongoDB computation is as follows:

A1: Connect to the MongoDB database. The IP and port number is localhost:27017, the database name is test and both the user name and the password are test. If any other parameters are needed, write them in line with the format mongo://ip:port/db?arg=value&…

A2: Fetch data from the MongoDB database using find function to create a cursor. The collection is person. The filtering criterion is null and the specified keys are name and gender. It can be seen that this find function is similar to the find function of MongoDB. By fetching and processing data in batches, the esProc cursor can avoid the memory overflow caused by big data importing.

A3: Since the data here are small, fetch function will fetch them all at once.

A4: Sort the data by name in ascending order, using sort function. Chinese is used in the data sorting. For the other localized languages esProc supports, please see below.

The result of operation is:

One thing to note is that esProc doesn't provide the java driver of MongoDB. To access MongoDB with esProc, the latter (a driver of 2.12.2 version or above is required, i.e. mongo-java-driver-2.12.2.jar) should be put into the [esProc installation directory]\common\jdbc beforehand.

The script for computation in MongoDB with the assistance of esProc is easy to integrate with Java program. By adding another line of code – A5, which is result A4, the result in the form of resultset can be output to Java program. For detailed code, please refer to esProc Tutorial. In the same way, to access MongoDB by calling esProc code with Java program also requires putting the java driver of MongoDB into the classpath of Java program.

The java driver of MongoDB can be downloaded from the following URL: https://github.com/mongodb/mongo-java-driver/releases。

esProc supports all the following Languages:

ja_JP       Japanese   Japan

es_PE     Spanish       Peru

en            English

ja_JP_JP   Japanese Japan

es_PA     Spanish       Panama

sr_BA     Serbian       Bosnia and Herzegovina

mk           Macedonian

es_GT    Spanish       Guatemala

ar_AE     Arabic         United Arab Emirates

no_NO   Norwegian          Norway

sq_AL     Albanian     Albania

bg            Bulgarian

ar_IQ      Arabic  Iraq

ar_YE     Arabic  Yemen

hu            Hungarian

pt_PT     Portuguese  Portugal

el_CY      Greek  Cyprus

ar_QA    Arabic  Qatar

mk_MK  Macedonian       Macedonia

sv            Swedish

de_CH    German      Switzerland

en_US    English        United States

fi_FI        Finnish        Finland

is             Icelandic

cs            Czech         

en_MT   English        Malta

sl_SI        Slovenian   Slovenia

sk_SK      Slovak         Slovakia

it              Italian

tr_TR      Turkish        Turkey

zh            Chinese

th            Thai

ar_SA     Arabic         Saudi Arabia

no            Norwegian         

en_GB    English        United Kingdom

sr_CS      Serbian       Serbia and Montenegro

lt              Lithuanian

ro            Romanian

en_NZ    English        New Zealand

no_NO_NY       Norwegian       Norway    Nynorsk

lt_LT       Lithuanian Lithuania

es_NI     Spanish       Nicaragua

nl             Dutch         

ga_IE      Irish   Ireland

fr_BE      French        Belgium

es_ES     Spanish       Spain

ar_LB     Arabic         Lebanon

ko            Korean

fr_CA      French        Canada

et_EE     Estonian     Estonia

ar_KW    Arabic         Kuwait

sr_RS      Serbian       Serbia

es_US     Spanish       United States

es_MX   Spanish       Mexico

ar_SD     Arabic         Sudan

in_ID      Indonesian          Indonesia         

ru            Russian

lv             Latvian

es_UY    Spanish       Uruguay

lv_LV       Latvian       Latvia

iw            Hebrew

pt_BR     Portuguese         Brazil

ar_SY      Arabic         Syria

hr            Croatian

et            Estonian

es_DO    Spanish       Dominican Republic

fr_CH     French        Switzerland

hi_IN      Hindi  India

es_VE     Spanish       Venezuela

ar_BH    Arabic         Bahrain

en_PH    English        Philippines

ar_TN     Arabic         Tunisia

fi              Finnish

de_AT     German      Austria

es            Spanish

nl_NL      Dutch          Netherlands

es_EC     Spanish       Ecuador

zh_TW   Chinese      Taiwan

ar_JO     Arabic         Jordan

be            Belarusian

is_IS        Icelandic    Iceland

es_CO    Spanish       Colombia

es_CR    Spanish       Costa Rica

es_CL     Spanish       Chile

ar_EG     Arabic         Egypt

en_ZA    English        South Africa

th_TH     Thai    Thailand

el_GR     Greek          Greece

it_IT        Italian         Italy

ca            Catalan

hu_HU   Hungarian Hungary

fr             French

en_IE      English        Ireland

uk_UA    Ukrainian   Ukraine

pl_PL      Polish Poland

fr_LU      French        Luxembourg

nl_BE      Dutch          Belgium

en_IN     English        India

ca_ES     Catalan      Spain

ar_MA   Arabic         Morocco

es_BO    Spanish       Bolivia

en_AU    English        Australia

sr             Serbian

zh_SG     Chinese      Singapore

pt            Portuguese        

uk            Ukrainian

es_SV     Spanish       El Salvador

ru_RU    Russian      Russia

ko_KR     Korean        South Korea

vi             Vietnamese

ar_DZ     Arabic         Algeria

vi_VN     Vietnamese        Vietnam

sr_ME    Serbian       Montenegro

sq            Albanian

ar_LY      Arabic         Libya

ar            Arabic

zh_CN    Chinese      China

be_BY    Belarusian Belarus

zh_HK    Chinese      Hong Kong

ja             Japanese

iw_IL      Hebrew      Israel

bg_BG    Bulgarian   Bulgaria

in             Indonesian         

mt_MT  Maltese     Malta

es_PY     Spanish       Paraguay

sl             Slovenian

fr_FR      French        France

cs_CZ     Czech          Czech Republic

it_CH      Italian         Switzerland

ro_RO    Romanian  Romania

es_PR     Spanish       Puerto Rico

en_CA    English        Canada

de_DE    German      Germany

ga            Irish

de_LU    German      Luxembourg

de            German

es_AR    Spanish       Argentina        

sk            Slovak

ms_MY  Malay         Malaysia

hr_HR    Croatian     Croatia

en_SG    English        Singapore

da            Danish

mt           Maltese

pl             Polish

ar_OM   Arabic         Oman

tr             Turkish

th_TH_TH         Thai Thailand   TH

el             Greek         

ms           Malay

sv_SE      Swedish      Sweden

da_DK    Danish        Denmark

es_HN    Spanish       Honduras