Achieving Foreign Key Joins between MongoDB Collections in SPL
As a distributed NoSQL database, MongoDB is very like a relational database. But since the documents in a single collection do not need to have the same set of fields, it performs poorly in handling joins, which is the strength of the relational databases. You have to hardcode a foreign key join with the MongoDB API, which is difficult and not intuitive. A simple alternative is the esProc SPL. Let’s look at how SPL deals with such a join.
Collection UserCourseProgress records users and the courses they attend. Courseid field is the foreign key pointing to _id field in collection Course. Now we need to get the count of users who attends each course and display the course name as the title field values in Course.
UserCourseProgress |
Course |
{"userId":"u01", |
{"_id":"c01" |
SPL code:
A |
|
1 |
=mongo_open("mongodb://localhost:27017/local?user=test&password=test") |
2 |
=mongo_shell(A1,"UserCourseProgress.aggregate([{$group: {_id: {'primary':'$courseid'},'userCount': {$sum: 1}}}, {$sort:{'userCount':-1}},{$project:{_id:0,'courseid':'$_id.primary','userCount':1}}])") |
3 |
=mongo_shell(A1,"Course.find(,{title:1})").fetch() |
4 |
=A2.switch(courseid,A3:_id) |
5 |
=A4.new(userCount,courseid.title) |
6 |
=mongo_close(A1) |
A1: Connect to MongoDB. The connection string format is mongo://ip:port/db?arg=value&….
A2: Count users of each course. MongoDB’s aggregate() function is used to retrieve data from UserCourseProgress, where the parameter is an aggregate expression written in MongoDB’s syntax. The query returns a memory result, as shown below:
A3: find() function retrieves data from Course with a filtering condition being absent and returns a cursor. Since there are only several courses, we use fetch() function to import data in the cursor to the memory. Below is the result:
A4: switch() function replaces A2’s foreign key field values with the corresponding records in A2, as shown below:
A5: Object style access to the memory to form a new two-dimensional table, as shown below:
A6: Close the MongoDB connection.
As you see, it’s as convenient, easy and intuitive to implement collection joins with SPL in MongoDB as performing joins in a relational database. SPL makes the loose table association in the NoSQL database not a problem anymore.
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