How Java Queries or Analyzes MongoDB Data

One approach is to use MongoDB’s official JSON-style syntax. It is reliable and stable, but the syntax is strange and difficult to learn, has inadequate computing ability with no support even for many basic operations, and produces rather complex code. Another is to use Hibernate Criteria of functional programming paradigm. The method is closer to natural language and easy to understand, but it has a heavy framework and weak computing ability. The third one is to use Calcite that supports the universal SQL. Both the costs of learning and computing ability are low. Besides, configurations are complicated, and users need to structuralize the collection.

esProc SPL is a better alternative. The open-source class library boasts remarkable computational capabilities, is easy to learn, does not require structuralizing the collection, has a light integration framework and is simple to configure.

SPL provides easy to use JDBC driver. It is quick to learn, even for beginners. For instance, a collection stores employee data, and the Orders field of each document stores orders data, and we are trying to perform a conditional query on all orders:

…
Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();
String str="= mongo_shell@x(mongo_open("mongodb://127.0.0.1:27017/mongo"),"test1.find()").fetch().conj(Orders). select(Amount>1000 && Amount<=3000 && like@c(Client,"*s*"))";
ResultSet result = statement.executeQuery(str);
…

The SPL code can be stored separately and independent, substantially decoupling it from the Java code and making it particularly suitable for handling complicated computations or computations that probably undergo frequent changes. The above code for achieving conditional query, for instance, can be saved as a SPL script file:


A

1

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

2

=mongo_shell@x(A1,"data.find()").fetch()

3

=A2.conj(Orders).select(Amount>1000 && Amount<=3000 && like@c(Client,"*s*"))

Then we can call the script file in Java as we call a stored procedure:

…
Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();
String str="call condition()";
ResultSet result = statement.executeQuery(str);
…

SPL has a wealth of built-in functions that provide computational capabilities as good as SQL. Below are more examples:


A

B

3


4

=A2.conj(Orders).groups(year(OrderDate);sum(Amount))

//Group and summarize orders documents

5

=A2.groups(Dept,Gender;count(1))

//Group and summarize employee documents

6

=A2.sort(Dept,-Salary)

//Sorting

7

=A2.id(State)

//Distinct

SPL also supports SQL syntax for the convenience of database programmers. The above SPL conditional query, for instance, can be rewritten as the following SQL:

$select OrderId, Client, Amount, OrderDate from {A2.conj()} where Client like '%S%' or (Amount>1000 and Amount<=2000)

SPL supplies simple and easy to use join functions, complementing the abilities of MongoDB and the other class libraries. To perform a join between employee documents and orders documents in a same collection, for instance:


A

1

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

2

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

3

=A2.new(Orders.OrderID,Orders.Client,   Name,Gender,Dept)

Another example. There are two collections having a relationship of main table and sub table and we want to left join them. The SPL code is as follows:


A

B

1

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

2

=mongo_shell@x(A1,"main.find()").fetch()

=mongo_shell@x(A1,"detail.find()").fethc()

3

=join@1(B2,cat;A2,cat)


4

=A3.new(_1.title,_1.regex,_1.cat,_2.path)

The SPL syntax is outstandingly expressive, making it easy to handle computations that are hard to deal with even with SQL and the stored procedure. To get the largest number of consecutively rising days of a stock, the core SPL code is only one line:


A

1

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

2

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

3

=a=0,A2.max(a=if(price>price[-1],a+1,0))

SPL supports multilevel data object, making it convenient to process the multilevel JSON stored in MongoDB. For example, according to the following collection, calculate the sum of income and output in each document.

_id

Income

Output

1

"cpu":1000, "mem":500,     "mouse":"100"

"cpu":1000, "mem":600   ,"mouse":"120"

2

"cpu":2000,"mem":1000,    "mouse":"50","mainboard":500

{"cpu":1500, "mem":300}

It is hard to do the computing task with all class libraries, including MongoDB’s own query syntax. Yet it becomes simple with SPL:


A

1

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

2

=mongo_shell@x(A1,"computer.find()").fetch()

3

=A2.new(_id:ID,income.array().sum():INCOME,output.array().sum():OUTPUT)

SPL has a professional IDE equipped with all-around debugging functionalities and letting users observe the result of each step, which is ideal for dealing with computations with complex logic:

 

In addition, SPL provides a lot of practical uses, such as the method for processing large amounts of data and mixed computing among CSV, XLS, RESTful, and various NoSQL databases. More information can be found HERE.