How to Do Joins in MongoDB
Key words: MongoDB Joins
MongoDB is a document-based NoSQL database that supports distributed storage. Its BSON format represents attributes better than relational databases do. But the NoSQL database isn’t that good at structured processing. It doesn’t give a strong enough support for operations such as joins, group and subqueries. For complicated computations, users have to read MongoDB data out to process. Here the problem is that it’s difficult to program those computations in Java.
Though MongoDB has $lookup to do simple join operations, programming multi-table joins is still not that easy, even complicated, in real-world businesses. To JOIN two sets, for example, we have part of the source data below:
Collection1: |
Collection2: |
user1 user2 income 1 2 0.56 1 3 0.26 |
user1 user2 output 1 2 0.3 1 3 0.4 2 3 0.5 |
Expected result:
user1 user2 income output 1 2 0.56 0.3 1 3 0.26 0.4 |
A Mongo script does it in this way:
db.c1.aggregate([ { "$lookup": { "from": "c2", "localField":"user1", "foreignField":"user1", "as": "collection2_doc" }}, { "$unwind":"$collection2_doc"}, { "$redact": { "$cond": [ {"$eq": [ "$user2", "$collection2_doc.user2"] }, "$$KEEP", "$$PRUNE" ] }}, { "$project": { "user1": 1, "user2": 1, "income": "$income", "output": "$collection2_doc. output" }} ]).pretty() |
The join would be rather simple if we could use eProc to do it. esProc SPL boasts a complete set of set-operation function library and agile syntax to handle it in a simple way. It helps make MongoDB great in structured processing. Here’s the 4-line SPL script for joining the two sets:
A |
|
1 |
=mongo_open("mongodb://127.0.0.1:27017/raqdb") |
2 |
=t1=mongo_shell(A1,"c1.find()").fetch(),t2=mongo_shell(A1,"c2.find()").fetch() |
3 |
=t1.join(user1:user2,t2:user1:user2,output) |
With the help of esProc SPL, we can conveniently handle structured data processing in MongoDB. For more examples, read esProc-driven MongoDB Hackers, Simplifying MongoDB Data Association, and How SPL Assists MongoDB Calculation.
esProc is integration-friendly. Read How to Call an SPL Script in Java to see how we can easily embedded an SPL script into a Java program.
Read Getting Started with esProc to download and install esProc, get a license for free and find related documentation.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL