RaqForum 35 No.
682 View  • 
     ...

How to Do Joins in MongoDB

 
MongoDB(19) join(11)

Key words: MongoDB Joins

MongoDB is a document-based NoSQL database that supports distributed storage. Its BSON format represents attributes better than relational databases do. But the NoSQL database isn’t that good at structured processing. It doesn’t give a strong enough support for operations such as joins, group and subqueries. For complicated computations, users have to read MongoDB data out to process. Here the problem is that it’s difficult to program those computations in Java.

Though MongoDB has $lookup to do simple join operations, programming multi-table joins is still not that easy, even complicated, in real-world businesses. To JOIN two sets, for example, we have part of the source data below:

Collection1:

Collection2:

user1  user2    income

1  2  0.56

1  3  0.26

user1  user2    output

1  2  0.3

1  3  0.4

2  3  0.5

Expected result:

user1  user2  income    output

1  2  0.56    0.3

1  3  0.26    0.4

A Mongo script does it in this way:

db.c1.aggregate([

{    "$lookup": {

"from":   "c2",

"localField":"user1",

"foreignField":"user1",

"as":   "collection2_doc"

}},

 {  "$unwind":"$collection2_doc"},

{    "$redact": {

  "$cond": [

{"$eq":   [ "$user2",   "$collection2_doc.user2"] },

"$$KEEP",

"$$PRUNE"

]

}},

{    "$project": {

"user1":   1,

"user2":   1,

"income":   "$income",

"output":   "$collection2_doc. output"

}}

]).pretty()

The join would be rather simple if we could use eProc to do it. esProc SPL boasts a complete set of set-operation function library and agile syntax to handle it in a simple way. It helps make MongoDB great in structured processing. Here’s the 4-line SPL script for joining the two sets:

A

1

=mongo_open("mongodb://127.0.0.1:27017/raqdb")

2

=t1=mongo_shell(A1,"c1.find()").fetch(),t2=mongo_shell(A1,"c2.find()").fetch()

3

=t1.join(user1:user2,t2:user1:user2,output)

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)
 
 •  682 View  •