* 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.