How to simplify MongoDB queries with esProc

The native query syntax of MongoDB is quite cumbersome, and simple tasks require long code. Complex calculations are even more difficult to implement, such as:

SPL assists MongoDB: Only keep the running total for the last item in the partition

SPL assists MongoDB: Replace substring in array of objects with nested objects

SPL assists MongoDB: Find multiple latest by filter criteria

esProc provides a MongoDB API with built-in powerful calculation functions that simplify MongoDB queries.

Next, let's try how to integrate esProc into an application.

Download and install esProc first, recommend standard version:

https://www.esproc.com/download-esproc/

Then download the esProc external library to access external data sources such as MongoDB, also from the above address. Extract the zip file of the external library to any directory, such as d:\esProcSTD\extlib Start the esProc IDE, open the menu "Tools ->Options", find the configuration item "External library directory", and navigate to the directory you just found.

After navigating to the external library directory, a list of data sources will be displayed, and check MongoCli in the list.

Restart the IDE, create a new script, write the following code, and load the data from Example 1:

	A
1	=mongo_open("mongodb://127.0.0.1:27017/local")
2	=mongo_shell@d(A1, "{'find':'grp_score','projection':{'_id':0}}")
3	=mongo_close(A1)

Press ctrl-F9 to execute, and you can see the execution result of A2 on the right side of the IDE, presented in the form of a data table, which is very convenient for debugging SPL code.

After simply loading data from MongoDB, SPL can be used to simplify complex MongoDB queries. The complete code for the first example is:

	A
1	=mongo_open("mongodb://127.0.0.1:27017/local")
2	=mongo_shell@d(A1, "{'find':'grp_score','projection':{'_id':0}}")
3	=mongo_close(A1)
4	=A2.select(seq>=arg1 && seq<=arg2)
5	=A4.group(grp;(a=~.sort(seq),a.m(-1).x=a.sum(score)))
6	=json(A4)

Filter the data through parameters first, and then write the aggregation value on the last record of this group. After running, you can see the result:

Save the above script in a directory, such as D:\data\grp_score.splx, which will be used in subsequent Java code.

Example 1 above is single-layer data, and Example 2 below is multi-layer data:

	A
1	=mongo_open("mongodb://127.0.0.1:27017/local")
2	=mongo_shell@d(A1, "{'find':'meetings','projection':{'_id':0}}")
3	=mongo_close(A1)
4	=A2.run(organizer.run(avatar="https://new.com/"+mid(avatar,17)), meetings.run(owner.run(avatar="https://new.com/"+mid(avatar,17)), participants.run(avatar="https://new.com/"+mid(avatar,17)) ) )

On the right side of the IDE, you can expand and observe multiple layers of data layer by layer, where the structure of A2 is as follows:

Similarly, save the above script in a directory, such as D:\data\meetings.splx. After debugging in the IDE, configure the Java application environment. Find the esProc JDBC related jars from the directory "[Installation directory]\esProc\lib": esproc-bin-xxxx.jar, icu4j_60.3.jar.

Deploy these two jars to the class path of the Java development environment. The jars of external libraries will be dynamically loaded through configuration files and do not require manual deployment. Find the esProc configuration file raqsoftConfig.xml from the directory "[Installation directory]\esProc\config" and deploy it to the Java development environment's class path.

There are two important configuration items: mainPath, this is the default path for scripts and other files; importLibs->lib, this is the enabled external library. It can be manually modified in the configuration file or through the configuration interface in the IDE. Next, you can write Java code to execute SPL through esProc JDBC. Let's try Example 1 first:

Class.forName("com.esproc.jdbc.InternalDriver");
Connection con= DriverManager.getConnection("jdbc:esproc:local://");
PreparedStatement st = con.prepareCall("call grp_score(?,?)");
st.setInt(1,1);
st.setInt(2,4);
ResultSet rs = st.executeQuery();

After running, you can see the result:

As can be seen, the process of esProc JDBC calling SPL scripts is the same as that of database JDBC calling stored procedures. Example 2 is similar, calling SPL script with file name in Java:

Class.forName("com.esproc.jdbc.InternalDriver");
Connection con= DriverManager.getConnection("jdbc:esproc:local://");
PreparedStatement st = con.prepareCall("call meetings()");
ResultSet rs = st.executeQuery();

The execution result looks like the following:

esProc also supports simple SQL, making it convenient for database programmers to use. For example, loading the data from Example 1 and then executing SQL, the script can be written as follows:

	A
1	=mongo_open("mongodb://127.0.0.1:27017/local")
2	=mongo_shell@d(A1, "{'find':'grp_score','projection':{'_id':0}}")
3	=mongo_close(A1)
4	$select case grp when 'A' then 'Class 1' when 'B' then 'Class 2' else 'others' end level,sum(score) as subtotal from {A2} where seq>=? and seq<=? group by grp ;arg1,arg2
5	return A4

Save the script file as grp_scoreSQL.splx, and the result after execution is as follows:

There are many examples of simplifying MongoDB queries on the esProc official website. Those interested can take a look.