MongoDB is a typical NoSQL database. Its document-oriented construction makes each storage and entry handy and environment friendly. However the database has somewhat weak computing capacity. Computations on MongoDB knowledge, notably complicated ones, are laborious to deal with. A knowledge computing engine having highly effective computing functionality is required to work with MongoDB to realize related computing duties.
The open-source esProc SPL is a specialised structured knowledge computation engine. It provides wealthy class libraries and all-around, database-independent computational capabilities. SPL has an impartial procedural syntax that’s notably good at dealing with complicated computations. It might probably assist MongoDB enhance its capacity to compute, accomplish grouping & aggregation, joins, subqueries, and all the opposite computing duties effortlessly.
Common queries
It’s simple to realize JOINs MongoDB finds it troublesome to deal with in SPL:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
Connect with MongoDB |
| 2 | =mongo_shell(A1,"c1.discover()").fetch() |
Fetch knowledge from MongoDB |
| 3 | =mongo_shell(A1,"c2.discover()").fetch() |
|
| 4 | =A2.be part of(user1:user2,A3:user1:user2,output) |
Carry out be part of |
| 5 | >A1.shut() |
Shut connection to MongoDB |
SPL can reuse the results of dealing with knowledge of a desk that’s repeatedly concerned in computations:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
|
| 2 | =mongo_shell(A1,"course.discover(,{_id:0})").fetch() |
Fetch knowledge from MongoDB |
| 3 | =A2.group(Sno).((avg = ~.avg(Grade), ~.choose(Grade>avg))).conj() |
Get paperwork the place grading stage is above common |
| 4 | >A1.shut() |
Carry out IN conditional question in SPL:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/take a look at") |
|
| 2 | =mongo_shell(A1,"orders.discover(,{_id:0})") |
Fetch knowledge from MongoDB |
| 3 | =mongo_shell(A1,"worker.discover({STATE:'California'},{_id:0})").fetch() |
Choose sure worker paperwork |
| 4 | =A3.(EID).kind() |
Get EID discipline and kind it |
| 5 | =A2.choose(A4.pos@b(SELLERID)).fetch() |
Carry out binary search |
| 6 | >A1.shut() |
SPL’s method to show overseas key values to things – the object-referencing overseas key – creates environment friendly overseas key pointers:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/native") |
|
| 2 | =mongo_shell(A1,"Progress.discover({}, {_id:0})").fetch() |
Fetch Progress knowledge |
| 3 | =A2.teams(courseid; depend(userId):popularityCount) |
Group and depend by course |
| 4 | =mongo_shell(A1,"Course.discover(,{title:1})").fetch() |
Get Course knowledge |
| 5 | =A3.change(courseid,A4:_id) |
Overseas-key-based be part of |
| 6 | =A5.new(popularityCount,courseid.title) |
Create consequence set |
| 7 | =A1.shut() |
SPL achieves APPLY algorithm in a easy approach:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
|
| 2 | =mongo_shell(A1,"customers.discover()").fetch() |
Fetch customers knowledge |
| 3 | =mongo_shell(A1,"exercises.discover()").fetch() |
Fetch exercises knowledge |
| 4 | =A2.conj(A3.choose(A2.exercises.pos(_id)).derive(A2.identify)) |
Get matching _id values from the sequence of exercises paperwork |
| 5 | >A1.shut() |
SPL’s approach of performing set-oriented calculations – intersection, union, distinction, and concatenation:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
|
| 2 | =mongo_shell(A1,"emp1.discover()").fetch() |
|
| 3 | =mongo_shell(A1,"emp2.discover()").fetch() |
|
| 4 | =[A2,A3].conj() |
Concatenation of sequences |
| 5 | =[A2,A3].merge@ou() |
Union by complete row comparability |
| 6 | =[A2,A3].merge@ou(_id, NAME) |
Union by key worth comparability |
| 7 | =[A2,A3].merge@oi() |
Intersection by complete row comparability |
| 8 | =[A2,A3].merge@oi(_id, NAME) |
Intersection by key worth comparability |
| 9 | =[A2,A3].merge@od() |
Distinction by complete row comparability |
| 10 | =[A2,A3].merge@od(_id, NAME) |
Distinction by key worth comparability |
| 11 | >A1.shut() |
Get sequence variety of a member in a sequence in SPL:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/native") |
|
| 2 | =mongo_shell(A1,"customers.discover({identify:'jim'},{identify:1,buddies:1,_id:0})").fetch() |
|
| 3 | =A2.buddies.pos("luke") |
Get sequence numbers of members in sequence buddies |
| 4 | =A1.shut() |
Carry out intersection of multi-member collections in SPL:
| A | B | |
|---|---|---|
| 1 | [Chemical, Biology, Math] | Programs |
| 2 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
|
| 3 | =mongo_shell(A2,"scholar.discover()").fetch() |
Fetch scholar knowledge |
| 4 | =A3.choose(Lesson^A1!=[]) |
Get paperwork the place at the very least one course is chosen |
| 5 | =A4.new(_id, Identify, ~.Lesson^A1:Lession) |
Get the ultimate consequence |
| 6 | >A2.shut() |
Advanced queries Getting TopN in SPL:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/take a look at") |
|
| 2 | =mongo_shell(A1,"last3.discover(,{_id:0};{variable:1})").fetch() |
Get last3 knowledge and kind it by variable |
| 3 | for A2;variable =A3.prime(3;-timestamp) |
Get the three paperwork having the most recent timestamps |
| 4 | `=@ | B3` |
| 5 | =B4.minp(~.timestamp) |
Get paperwork with the earliest timestamp |
| 6 | >mongo_close(A1) |
Summarize a nested-structure assortment in SPL:
| A | |
|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
| 2 | =mongo_shell(A1,"pc.discover()").fetch() |
| 3 | =A2.new(_id:ID,earnings.array().sum():INCOME,output.array().sum():OUTPUT) |
| 4 | >A1.shut() |
Mix subdocuments made up of a number of attributes in SPL:
| A | B | C | |
|---|---|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/native") |
||
| 2 | =mongo_shell(A1,"c1.discover(,{_id:0};{identify:1})").fetch() |
||
| 3 | =create(_id, readUsers) |
Create consequence desk sequence | |
| 4 | `for A2;identify =A4.conj(acls.learn.customers | acls.append.customers | acls.edit.customers |
| 5 | {% uncooked %}`=@ | A3.insert(0, A4.identify, B4)` | |
| 6 | =A1.shut() |
Question nested Record subdocument in SPL:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/native") |
|
| 2 | =mongo_shell(A1,"Cbettwen.discover(,{_id:0})").fetch() |
|
| 3 | =A2.conj((t=~.objList.knowledge.dataList,t.choose((s=float(~.break up@c1()(1)), s>6154 && s<=6155)))) |
Get eligible strings |
| 4 | =A1.shut() |
SPL Cross-sector aggregation:
| A | |
|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/native") |
| 2 | =mongo_shell(A1,"scholar.discover()").fetch() |
| 3 | =A2.group(faculty) |
| 4 | =A3.new(faculty:faculty,~.align@a(5,sub1).(~.len()):sub1,~.align@a(5,sub2).(~.len()):sub2) |
| 5 | =A4.new(faculty,sub1(5):sub1-5,sub1(4):sub1-4,sub1(3):sub1-3,sub1(2):sub1-2,sub1(1):sub1-1,sub2(5):sub2-5,sub2(4):sub2-4,sub2(3):sub2-3,sub2(2):sub2-2,sub2(1):sub2-1) |
| 6 | =A1.shut() |
SPL segment-based grouping:
| A | B | |
|---|---|---|
| 1 | [3000,5000,7500,10000,15000] | Intervals of Gross sales for segmentation |
| 2 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
|
| 3 | =mongo_shell(A2,"gross sales.discover()").fetch() |
|
| 4 | =A3.teams(A1.pseg(~.SALES):Phase;depend(1): quantity) |
Group knowledge and depend workers by SALES intervals |
| 5 | >A2.shut() |
SPL class-based grouping:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
|
| 2 | =mongo_shell(A1,"books.discover()") |
|
| 3 | =A2.teams(addr,e-book;depend(e-book):Depend) |
Grouping & depend |
| 4 | =A3.teams(addr;sum(Depend):Whole) |
Grouping & sum |
| 5 | =A3.be part of(addr,A4:addr,Whole) |
Be a part of operation |
| 6 | >A1.shut() |
Information writing Export knowledge as CSV in SPL:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/raqdb") |
|
| 2 | =mongo_shell(A1,"carInfo.discover(,{_id:0})") |
|
| 3 | =A2.conj((t=~,vehicles.automotive.new(t.id:id,t.vehicles.identify, ~:automotive))) |
Break up every automotive discipline worth into a number of rows |
| 4 | =file("D:knowledge.csv").export@tc(A3) |
Export as CSV |
| 5 | >A1.shut() |
SPL database replace (from MongoDB to MySQL):
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/raqdb") |
Connect with MongoDB |
| 2 | =mongo_shell(A1,"course.discover(,{_id:0})").fetch() |
|
| 3 | =join("myDB1") |
Connect with MySQL |
| 4 | =A3.question@x("choose * from course2").keys(Sno, Cno) |
|
| 5 | >A3.replace(A2:A4,course2,Sno,Cno, Grade; Sno,Cno) |
Replace knowledge into MySQL |
| 6 | >A1.shut() |
SPL database replace (from MySQL to MongoDB):
| A | B | |
|---|---|---|
| 1 | =join("mysql") |
Connect with MySQL |
| 2 | =A1.question@x("choose * from course2") |
Get knowledge of course2 desk |
| 3 | =mongo_open("mongodb://localhost:27017/raqdb") |
Connect with MongoDB |
| 4 | =mongo_insert(A3, "course",A2) |
Insert data of MySQL desk course2 into the MongoDB assortment |
| 5 | >A3.shut() |
Combined computations
SPL allows handy blended computation between MongoDB and one other knowledge supply:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/take a look at") |
Connect with MongoDB |
| 2 | =mongo_shell(A1,"emp.discover({'$and':[{'Birthday':{'$gte':'"+string(begin)+"'}},{'Birthday':{'$lte':'"+string(end)+"'}}]},{_id:0})").fetch() |
Get data inside a specified time interval |
| 3 | =A1.shut() |
Shut MongoDB connection |
| 4 | =myDB1.question("choose * from cities") |
Get knowledge of cities desk in MySQL |
| 5 | =A2.change(CityID,A4:CityID) |
Overseas-key-based be part of |
| 6 | =A5.new(EID,Dept,CityID.CityName:CityName,Identify,Gender) |
Create consequence set |
| 7 | return A6 |
Return the consequence set |
SQL help
Apart from the native syntax, SPL presents help of SQL92 normal. You should use SQL to question MongoDB. To attain the above be part of operation, as an example:
| A | |
|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/take a look at") |
| 2 | =mongo_shell(A1,"c1.discover()").fetch() |
| 3 | =mongo_shell@x(A1,"c2.discover()").fetch() |
| 4 | $choose s.* from {A2} as s left be part of {A3} as r on s.user1=r.user1 and s.user2=r.user2 the place r.earnings>0.3 |
Integration into utility
SPL offers normal JDBC/ODBC drivers by means of which SPL will be conveniently built-in into an utility or invoked by it. To invoke SPL code by means of JDBC, as an example:
…
Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn = DriverManager.getConnection("jdbc:esproc:native://");
PrepareStatement st=con.prepareStatement("name splScript(?)"); // splScript is the identify of SPL script file
st.setObject(1,"California");
st.execute();
ResultSet rs = st.getResultSet();
…
With all these functionalities, you’ll certain to be impressed by MongoDB’s strikingly boosted computing capacity. Attempt your hand now.
Check GitHub Repo
Remember to present us a star ⭐ on the repo
