RaqForum 35 No.
572 View  • 
     ...

How to calculate subqueries in MongoDB

 
MongoDB(19) join(11)

Key words：Mongodb subquery  Mongodb join

Mongodb is indeed more powerful than relational database in some aspects (such as the throughput of additional log data), but it is weaker in structured computing. For example, mongodb does not support subqueries. When encountering these complex operations, you can only read out the data first and then calculate it, and it is not easy to write such calculations in Java and other languages.

For example, to process such a scenario: Find out the order information, and the sellerid in the order must be the employee ID of state = California in the employee collection. If it is written as SQL, it is:

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

The data volume of orders is large and cannot be retrieved at one time. The data volume of employee is small and the data volume of final result is small. Partial data is as follows:

MongoDB Collection orders:

…

{"_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" }

…

MongoDB Collection employee:

…

{"_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 }

…

Calculation results：

undefined

It would be much easier with the help of esProc. It's a programming language specially designed for structured and semi-structured data calculation. There are function class libraries for query, subquery, filter after grouping, etc., which can make up for Mongodb's lack of calculation ability in the structural aspect. For example, the above question can be solved in four lines:

A

1

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

2

=mongo_shell(A1,"orders.find(,{_id:0})")

3

=mongo_shell@x(A1,"employee.find({STATE:'California'},{_id:0})").fetch()

4

=A2.select(A3.(EID).sort().pos@b(SELLERID)).fetch()

With the help of esProc SPL, we can conveniently handle structured data processing in MongoDB. For more examples, read esProc-driven MongoDB Hackers, Simplifying MongoDB Data Association, and How SPL Assists MongoDB Calculation.

esProc is integration-friendly. Read How to Call an SPL Script in Java to see how we can easily embedded an SPL script into a Java program.

Read Getting Started with esProc to download and install esProc, get a license for free and find related documentation.

MongoDB(19) join(11)
 
 •  572 View  •