* Implement IN Subqueries in MongoDB
As a NoSQL database, MongoDB stores data in unfixed schemas. Each MongoDB document can have its own fields. But the database is not so good at handling structured computations. One instance is its lack of support for subquery. So we have to move data out of MongoDB for further computation when encountering complicated scenarios.
Here are two collections ORDERS and DEALERS. We want to find orders of New York according to the condition that SELLERIDs of ORDERS collection must equal to PIDs of DEALER documents where the STATE is New York. The SQL statement for expressing this is as below:
Select * from ORDERS where SELLERID in (select PID from DEALER where STATE='New York')
ORDERS |
ORDERID |
CLIENT |
SELLERID |
AMOUNT |
ORDERDATE |
30 |
YZ |
19 |
14600.0 |
2019-11-29 |
|
31 |
QHHW |
6 |
13800.0 |
2019-12-01 |
|
32 |
SAVEA |
9 |
5684.0 |
2019-12-08 |
|
… |
… |
DEALER |
PID |
NAME |
SURNAME |
GENDER |
STATE |
BIRTHDAY |
3 |
Rebecca |
Moore |
F |
New York |
1974-11-20 |
|
4 |
Ashley |
Wilson |
F |
California |
1980-07-19 |
|
5 |
Rachel |
Johnson |
F |
New Mexico |
1970-12-17 |
|
… |
… |
It would be easier to implement an IN subquery if you choose to use esProc SPL.
Download esProc installation package HERE.
Directions for implementing the above task with esProc:
1. Write SPL script dealer.dfx in esProc:
A |
B |
|
1 |
=mongo_open("mongodb://127.0.0.1:27017/raqdb") |
/ Connect to MongoDB database |
2 |
=mongo_shell(A1,"ORDERS.find(,{_id:0})") |
/ Query data of collection ORDERS |
3 |
=mongo_shell(A1,"DEALER.find({STATE:'New York '},{PID:1, _id:0})").fetch() |
/ Query PID field of collection DEALERS |
4 |
=A3.(PID).sort() |
/ Get PID field and sort it |
5 |
=A2.select(A4.pos@b(SELLERID)).fetch() |
/ Get ORDERS documents where SELLERID is equal to PID |
6 |
>A1.close() |
/ Close database connection |
2. Execute the script and return the following result:
A5 |
ORDERID |
CLIENT |
SELLERID |
AMOUNT |
ORDERDATE |
31.0 |
QHHW |
6.0 |
13800.0 |
2019-12-01 |
|
32.0 |
SAVEA |
9.0 |
5684.0 |
2019-12-08 |
|
… |
… |
esProc offers JDBC interface, so you can easily integrate the script into a Java program:
public static void doWork() {
Connection con = null;
java.sql.Statement st;
try{
Class.forName("com.esproc.jdbc.InternalDriver");
con = DriverManager.getConnection("jdbc:esproc:local://");
// Call script dealer.dfx
st=con.createStatement();
ResultSet rst = st.executeQuery("call dealer");
System.out.println(rst);
} catch(Exception e){
System.out.println(e);
} finally{
// Close database connection
if (con!= null) {
try {
con.close();
} catch(Exception e) {
System.out.println(e);
}
}
}
}
Similarly, you can also implement EXISTS, ANY and ALL subqueries with esProc SPL.
Read How to Call an SPL Script in Java to learn more about integration of esProc SPL script into a Java program.
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