Shulou(Shulou.com)06/03 Report--

[abstract]

The calculation of subquery of table data by Mongodb is not as convenient as that of relational database, but it is much easier to implement through the aggregator SPL language, and there is no need to worry about the operation of subquery by Mongodb.

In the daily development of Mongodb, we often encounter the problem of sub-query associated with two or more data tables. If it is a relational database, the subqueries in it are relatively familiar and easy to implement. However, Mongodb's support for subqueries is not friendly enough, so when it comes to these complex operations, we can only read out the data first and then calculate them, and it is not very easy to write such calculations in languages such as Java. At this point, we can consider using the aggregator SPL language to assist in the implementation of subqueries. SPL has rich interfaces and concise syntax, which can greatly facilitate users to use Mongodb, so that subquery writing is more standardized and unified, efficient and efficient, as illustrated by examples below.

The order data is saved in the document orders and the employee data is saved in employee, as follows:

> db.orders.find ()

{"_ id": ObjectId ("5434f88dd00ab5276493e270"), "ORDERID": 1, "CLIENT": "UJRNP", "SELLERID": 17, "AMOUNT": 392, "ORDERDATE": "15:28 on 2008-11-2"}

{"_ id": ObjectId ("5434f88dd00ab5276493e271"), "ORDERID": 2, "CLIENT": "SJCH", "SELLERID": 6, "AMOUNT": 4802, "ORDERDATE": "15:28 on 2008-11-9"}

{"_ id": ObjectId ("5434f88dd00ab5276493e272"), "ORDERID": 3, "CLIENT": "UJRNP", "SELLERID": 16, "AMOUNT": 13500, "ORDERDATE": "15:28 on 2008-11-5"}

{"_ id": ObjectId ("5434f88dd00ab5276493e273"), "ORDERID": 4, "CLIENT": "PWQ", "SELLERID": 9, "AMOUNT": 26100, "ORDERDATE": "15:28 on 2008-11-8"}

...

> db.employee.find ()

{"_ id": ObjectId ("5437413513bdf2a4048f3480"), "EID": 1, "NAME": "Rebecca", "SURNAME": "Moore", "GENDER": "F", "STATE": "California", "BIRTHDAY": "1974-11-20", "HIREDATE": "2005-03-11", "DEPT": "rotated", "SALARY": 7000}

{"_ id": ObjectId ("5437413513bdf2a4048f3481"), "EID": 2, "NAME": "Ashley", "SURNAME": "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", "SURNAME": "Johnson", "GENDER": "F", "STATE": "New Mexico", "BIRTHDAY": "1970-12-17", "HIREDATE": "2010-12-01", "DEPT": "Sales", "SALARY": 9000}

...

Request to find out the order information, where the SELLERID of the order must be the employee id of STATE= California in the employee collection. If it is written as sql, it means:

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

At the same time, the large amount of data in Orders can not be taken out at once, while the amount of data in employee is small, and the amount of data in the final result is also small.

The code to use the aggregator SPL is as follows:

A

1=mongo_open ("mongodb://localhost:27017/test?user=test&password=test")

2=mongo_shell (A1, "orders.find (, {_ id:0})")

3=mongo_shell (A1, "employee.find ({STATE:'California'}, {_ id:0})") .fetch ()

4=A3 (EID) .sort ()

5=A2.select (A4.pos@b (SELLERID)) .fetch ()

6 > mongo_close (A1)

A1: connect MongoDB, the connection word format is mongo://ip:port/db?arg=value& ….

A2: use the find function to fetch a number from orders to form a cursor. The filter condition is empty and the key _ id is not taken out. SPL cursors read and process data in batches to avoid excessive amounts of data and memory overflows.

A3: get the data in employee, provided that STATE= "California".

A4: sorts the EID in A3 (employee).

A5: filter orders data by condition that SELLERID must be in A4, which is equivalent to SELLERID in A4. Among them, the function pos implements the filter condition and detects whether the SELLERID value of order exists in the EID of employee. Here, all the data results are taken out at once, and if the results are large, they can also be taken out in batches, such as the first 1000 rows: fetch (1000).

The results are as follows:

A6: close the Mongodb connection.

In this example, A2Magi A3 gets the data related to the two tables, and select in A5 gets the data that meets the criteria to implement the subquery of the table.

It is important to note that the aggregator esProc does not contain mongodb's java driver package. If you want to use esProc to access mongodb, you must place mongodb's java driver package (for example: mongo-java-driver-2.12.2.jar) in advance under the external library directory extLib\ MongoCli set by the aggregator.

In addition to calculating directly in the aggregator, the above scripts that use SPL to assist mongodb calculation can also be easily integrated into java. As long as an additional line is added and written as return A5, you can output the results in the form of resultset to java. For specific code, refer to the esProc tutorial. Similarly, using java to call esProc to access mongodb must also put the java driver package of mongdb into the classpath of the java program.

Related articles:

MongoDB join operation

MongoDB foreign key association

MongoDB localized sorting

MongoDB subquery

MongoDB grouping statistics

MongoDB packet topN

MongoDB association operation

MongoDB subdocument List field

Assist in cross-summary of MongoDB calculations

Export MongoDB to csv

MongoDB join to assist in report development

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.