One approach is to start MongoDB Connector and execute SQL through MySQL JDBC. The official tool is perfectly compatible, almost without configurations. Yet its computing ability is weak – the official advice is to use it only for BI tools.

An alternative is using Calcite class library to execute general SQL on MongoDB. The solution is easily migratable but offers extremely weak computing ability. It does not support even the fuzzy query and date functions, needs to convert all multilevel collections into single-level ones – otherwise, some computations cannot be achieved, and is characterized by complicated configurations.

The wise choice is the open-source esProc SPL. Like Calcite, the library has similar principles and executes general SQL on MongoDB. Unlike Calcite, SPL boasts outstanding computational capabilities, does not need to re-constructure MongoDB collections, and is easy to configure.

SPL has simple basic uses. Suppose we have a two-level collection, where the first level stores employee data, under which the array type Orders field stores orders of the current employee. Now we are trying to perform a conditional query on the second-level orders. To do this, the Java code below is enough:

…
Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();
String str="$select * from
{mongo_shell@x(A1,mongo_open(\"mongodb://127.0.0.1:27017/mongo\"),\"data.find()\").conj(Orders)}
where Client like '%S%' or (Amount>1000 and Amount<=2000)";
ResultSet result = statement.executeQuery(str);
…

The SPL code can be stored separately and independently, 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 an SPL script file:

        To call it in Java, we just need to reference the script file name in the way of calling 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 supports commonly-used SQL syntax to be able to achieve various daily analyses. Here are some examples:

#sort  
$select * from {A3} order by Client,Amont desc

#group by  
$ select year(orderdate) y,sum(amount) s from {A3} group by year(orderdate) having sum(amount)>=2000000

#distinct  
$ select distinct(sellerid) from {A3}

SPL also supports advanced SQL syntax for achieving more complex and difficult computations, including set-oriented calculations, case when statement, with a statement and nested subqueries. Find more details in Examples of SQL Queries on Files.

In the above SQL statements, {…} represents a SPL extension function, which has equal computing ability as SQL yet boasts concise code. To perform a join query, for instance, we can use a SPL extension function instead of SQL:

SPL extension functions can simplify complex computing logic. To get the largest number of consecutively rising days for a stock, for instance, SPL just needs two lines of code:

MongoDB is often used to store JSON data. SQL is intended to compute structured records and is not suited to handling multilevel JSON. SPL, however, has a multilevel data object, making it the ideal tool for simplifying multilevel JSON computations. To sum income and output in each document in the following collection, for instance:

This is a rather difficult task, hard to handle in SQL or stored procedure, and even with MongoDB query syntax. It becomes simple with SPL：

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

SPL also features simple configurations and supports handling CSV, XLS, RESTful, and various NoSQL databases in SQL or with extension functions. Learn more about SPL HERE.