MongoDB does not support SQL functions. The existing open-source MongoDB JDBC driver works on the principle that SQL queries are verified and converted to MongoDB queries and then executed by calling MongoDB Java API, which provides translation tools and services from SQL to MongoDB query language. In MongoDB, there is a multi-layered nested structure. Attribute fields appear arbitrarily. It is not easy to describe clearly which field information of which layer is selected. Therefore, the open-source MongoDB JDBC driver only supports simple SQL. Not common SQL usages such as field expressions, nested queries, and associated queries are not supported, which are difficult to use in actual work. If you use the Open-esProc calculation package, you can first query through MongoDB. Next, combine with Open-esProc’s built-in SPL grammar (SQL-like calculations), which can complete all the functions of SQL and specialize in processing multi-layer data.

Let me give a simple example to illustrate how to use SPL. MongoDB has a collection named test1, which stores employee information, and the Orders field is an array type, which keeps multiple orders of the current employee. Thus, part of the data is as follows:

[{
      "_id": {"$oid":   "6074f6c7e85e8d46400dc4a7"},
      "EId": 7,"State":  "Illinois","Dept": "Sales","Name":   "Alexis",
       "Gender": "F","Salary":   9000,"Birthday": "1972-08-16",
      "Orders": [
         {"OrderID":70,"Client": "DSG","SellerId": 7,
         "Amount": 288,"OrderDate": "2009-09-30"},
         {"OrderID":131,"Client": "FOL","SellerId": 7,
         "Amount": 103.2,"OrderDate": "2009-12-10"}
    ]
}
{
      "_id": {"$oid":   "6074f6c7e85e8d46400dc4a8"},
      "EId": 8,"State": "California", ...
}]

SPL handles the conditional query on multi-layer collections in the following way:

According to A2, esProc supports MongoDB's JSON-style query expressions (find, count, distinct and aggregate). To do an interval-based query, the SPL is like this: =mongo_shell(A2,"test1.find({Orders.Amount:{gt:1000,gt:1000,lt:3000}})").

This block of code can be executed in esProc IDE, and stored as a script file (like select.dfx) for invocation from a Java program through the JDBC interface. Below is the code for invocation:

package Test;
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.Statement;
  public class test1 {
      public static void main(String[]   args)throws Exception {
            Class.forName("com.esproc.jdbc.InternalDriver");
          Connection connection   =DriverManager.getConnection("jdbc:esproc:local://");
          Statement statement =   connection.createStatement();
          ResultSet result =   statement.executeQuery("call select()");

……
          if(connection != null)   connection.close();
      }
  }

SPL achieves grouping and aggregation using the following code:

Or implements a join operation using code below:

The SPL data structure itself is multi-layered, which can directly correspond to multi-layer collections and can naturally express the master-child relationship, so there is no need for other associations. However, other computing libraries are single-layer data structures, which are difficult to correspond to a multi-layer collection.

SPL supports joining two single-level collections, of course:

SPL has rich expressions with procedural syntax and the support of SQL syntax. As SQL does not support multilevel data, SQL in SPL supports the join of two single-level collections, as shown by the code below:

For more examples, please refer to Assist MongoDB Calculation(pdf)

For more SPL applications, please refer to Use SPL in applications