MongoDB, as a mainstream NoSQL database, has become a powerful tool for handling unstructured data thanks to its flexible document structure. However, as anyone who’s used it knows, its computing capabilities leave much to be desired.
The trade-off of NoSQL is sacrificing the simplicity of SQL. Let’s take the example of finding the top 10 customers by order amount. With SQL, you can accomplish the task with a single SELECT TOP 10 statement. However, with MongoDB, you’ll need to use these three operators: $group, $sort, and $limit, and implementing cross-collection joins requires painstakingly chaining multiple $lookup stages – akin to manually assembling building blocks step by step. What’s even more frustrating is that advanced operations like window functions simply can’t be implemented using MongoDB’s native syntax.
Clearly, it wouldn’t be appropriate to import the data into MySQL for calculation; otherwise, why take the trouble to use MongoDB? If you were to brute-force the calculations in Java at the application layer, that would be incredibly stressful and not worth the effort.
With esProc SPL, it becomes much simpler as SPL can directly perform SQL-style computations on MongoDB data. For example, for a multi-layer nested order structure:
{ "_id": ObjectId("..."),
"order_id": "12345",
"customer": "C001",
"order_date": ISODate("2025-02-12T00:00:00Z"),
"order_details": [
{
"product_id": "P001",
"product_name": "Laptop",
"quantity": 2,
"price": 150.00,
"total": 300.00
},
...
]}
To query the top 10 largest customers and their order amounts, the SPL code would be:
The key is just one line in A3, which is as concise as SQL.
esProc SPL even allows the direct use of SQL syntax:
$select TOP 10 Customer, SUM(Amount) AS TotalAmount from {mongo_shell@d(mongo_open("mongodb://127.0.0.1:27017/mongo"),"orders.find()")}
group by Customer
SPL has a natural affinity for handling nested JSON data. In MongoDB, where documents with three levels of nesting are common, using SPL’s dot operator A.B.C allows direct traversal to the deepest level. When dealing with arrays, SPL can also use conj()for expansion and groups() for grouping and statistical analysis. This harmonious balance – ‘you ensure efficient storage; I guarantee rapid computation’ – is a genuine partnership.
This is even better news for Java developers. Previously, manipulating MongoDB required creating a bunch of BasicDBObject objects. Now, simply adding a few jars allows you to execute SPL statements (and the aforementioned SQL statements), and even use a script file as a stored procedure. This essentially transforms MongoDB’s interface to something resembling a relational database, reducing code volume by 90% – shifting from cumbersome pipeline operations to the clean style of Class.forName(…).execute(…).
Class.forName("com.esproc.jdbc.InternalDriver");
Connection con= DriverManager.getConnection("jdbc:esproc:local://");
Statement st = con.prepareCall("call SPL_Mongo_Example()");
st.execute();
ResultSet rs = st.getResultSet();
Here, SPL_Mongo_Example is the name of the SPL script (SPL_Mongo_Example.splx). Just like calling a stored procedure, the code no longer involves data processing, and is simple and clear.
The fly in the ointment is that esProc SPL is developed in Java. Calling it from other languages like Python/C++ requires using an HTTP interface, unlike Java applications which can integrate seamlessly. But considering its ability to make MongoDB directly unlock SQL computational capabilities, this minor flaw is entirely forgivable. After all, in this data-driven era, a tool that saves you from writing 200 lines of aggregation code deserves the compliment “That’s awesome.”
esProc SPL is open-source, and those interested are welcome to give it a try 👉 Open-Source Address.
Top comments (4)
esProc SPL unleashing MongoDB's Potential is a great idea
Thank you for sharing your thoughts! esProc SPL’s structured approach can indeed enhance MongoDB’s analytical capabilities, making complex queries more intuitive and efficient.
Great sharing！
Thanks for your support! 🙌🏻