The MongoDB official query language can handle joins. It achieves left joins using $lookup function and other types of joins by modifying $lookup statements. The cross join, however, almost cannot be coded through such modifications. Besides, the JSON-style syntax is complex, diffuse, and difficult to use.

Calcite that supports general SQL is able and gives rather good support for various types of joins. But it has big issues. The overall computing ability is extremely weak – even without fuzzy queries and date functions. It does not support direct joins within a multilevel collection; rather, it will re-structure the collection into single-level collections.

The best approach is to use esProc SPL. The open-source class library can handle all types of joins using simple and convenient functions, supports direct joins within a multilevel collection, and possesses powerful computational capabilities.

SPL has JOIN functions to do joins. Their uses are simple and intuitive. For instance, we have two collections that have a relationship of main table and sub table. Both have the cat field and we are trying to perform an inner join and then grouping & aggregation. Below is the SPL script file for doing this:

Then we can invoke the script file by name via JDBC in Java:

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

SPL offers methods for handling all types of joins, which are easy to learn. The single join means an inner join. A left join is represented by join@1, where @1 is the option, and a full join is join@f. For cross join, SPL offers xjoin function to specifically handle it.

SPL also supports doing joins using SQL syntax, for the convenience of database programmers. We can code a left join as follows:

Learn more about SPL SQL syntax in Examples of SQL Queries on Files.

SPL does natural joins within multilevel collections efficiently using simple code. We have a collection storing employee data, and for each employee, there is an Orders field containing orders data. The task is to join employees and orders. SPL has the following code for a natural join”:

SPL allows writing code directly within a Java program. This is convenient and fits for computations having simple code or whose code will remain rather stable. But this approach has tighter coupling than an independent script file. A natural join on a multilevel collection can be written as follows:

…
Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();
String str="=mongo_open(\"mongodb://127.0.0.1:27017/mongo\"). mongo_shell@x(A1,\"data.find()\").fetch().new(Orders.Client:Client, Orders.Amount:Amount, Name, Dept, Salary)";
ResultSet result = statement.executeQuery(str);
…

SPL also supports joins between MongoDB and any other data source, such as RESTful, relational databases, files, and all types of NoSQL databases. Take the relational database as an example:

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:

Apart from extraordinary computational capabilities and simple and easy-to-learn syntax, SPL can deal with joins on large amounts of data and high-performance joins. Find relative information HERE.