The aggregator assists in child queries of MongoDB calculations

MongoDB does not support subqueries, and when encountering these complex operations, you can only read the data first and then calculate them, and it is not very simple to write such calculations in Java and other languages. At this time, you can consider using the integrator esProc to assist in the implementation. Let's take a look at the specific practice through an example.

The document orders in MongoDB holds order data, and employee holds employee data. As follows:

> db.orders.find();

{ "_id" :ObjectId("5434f88dd00ab5276493e270"), "ORDERID" : 1,"CLIENT" : "UJRNP

", "SELLERID" : 17,"AMOUNT" : 392, "ORDERDATE" : "2008/11/2 15:28" }

{ "_id" :ObjectId("5434f88dd00ab5276493e271"), "ORDERID" : 2,"CLIENT" : "SJCH"

, "SELLERID" : 6,"AMOUNT" : 4802, "ORDERDATE" : "2008/11/9 15:28"}

{ "_id" :ObjectId("5434f88dd00ab5276493e272"), "ORDERID" : 3,"CLIENT" : "UJRNP

", "SELLERID" : 16,"AMOUNT" : 13500, "ORDERDATE" : "2008/11/5 15:28"}

{ "_id" :ObjectId("5434f88dd00ab5276493e273"), "ORDERID" : 4,"CLIENT" : "PWQ",

"SELLERID" : 9, "AMOUNT" :26100, "ORDERDATE" : "2008/11/8 15:28" }

…

> db.employee.find();

{ "_id" :ObjectId("5437413513bdf2a4048f3480"), "EID" : 1,"NAME" : "Rebecca", "

SURNAME" : "Moore","GENDER" : "F", "STATE" : "California","BIRTHDAY" : "1974-1

1-20", "HIREDATE" :"2005-03-11", "DEPT" : "R&D","SALARY" : 7000 }

{ "_id" :ObjectId("5437413513bdf2a4048f3481"), "EID" : 2,"NAME" : "Ashley", "S

URNAME" : "Wilson","GENDER" : "F", "STATE" : "New York","BIRTHDAY" : "1980-07-

19", "HIREDATE" :"2008-03-16", "DEPT" : "Finance","SALARY" : 11000 }

{ "_id" :ObjectId("5437413513bdf2a4048f3482"), "EID" : 3,"NAME" : "Rachel", "S

URNAME" : "Johnson","GENDER" : "F", "STATE" : "New Mexico","BIRTHDAY" : "1970-

12-17", "HIREDATE" :"2010-12-01", "DEPT" : "Sales","SALARY" : 9000 }

…

The requirement in the example is to find order information, requiring that the SELLERID in the order must be the employee id of STATE= California in the employee set. If written as sql:

Select * fromorders where orders.sellerid in (select eid from employee where employee.state='California')。

Orders has a large amount of data that cannot be taken out at once. The employee has a smaller amount of data, and the final result has a smaller amount of data.

The script that helps mongodb implement this subquery with the aggregator esProc is as follows:

A1: Connect to mongodb database, ip and port number are localhost:27017, database is test, username and password are both test.

A2: Use the find function to take numbers from mongodb to form cursors. Set is orders, filter condition is empty, specified key_id is not taken out. esProc uses the same argument format in the find function as montdb's find statement. esProc cursors read and process data in batches to avoid excessive data volume and memory overflow.

A3: Get data from employee if STATE="California".

A4: Sort the EIDs in A3(employee).

A5: Filter the orders data according to the condition that SELLERID must be in A4, which is equivalent to SELLERIDin A4. Take out the results once. If the result is large, it can also be taken out in batches, such as taking out the first 1000 rows: fetch(1000).

The result is:

Note that esProc does not include the java driver package for mongodb. To access mongodb with esProc, the java driver package for mongodb (e.g. mongo-java-driver-2.12.2.jar) must be placed in [esProc installation directory]\common\jdbc in advance.

The script that the integrator esProc assists mongodb to calculate is easy to integrate into java. As long as you add a line and write resultA6, you can output the resultset form to java. Refer to esProc tutorial for specific code. Similarly, calling esProc in java to access mongodb requires putting the java driver package for mongodb into the classpath of the java program.