Join is a very basic operation in SQL, but MongoDB does not provide it directly. It needs to use $lookup to deal with it, but it is troublesome to join multiple fields. For example:

db.getCollection(‘c1’).aggregate([
{
    $lookup:
        {
          from: “c2”,
          localField: “user1”,
          foreignField: “user1”,
          as: “R”
        }
},  
{$unwind :“$R”},
{ 
     $project: { 
         user1:1,
         user2:1,
         income:1,
         output:‘$R.output’,
         mid: {$cond: [ { $eq: [ ‘$user2’, ‘$R.user2’] }, 1, 0 ] }
     } 
},
{$match : { “$and”: [
        {mid : 1},
        {income:{ $gte: 0.30}} 
        ]
  }}
])

It’s equivalent to the simple SQL: SELECT S.* FROM C1 S LEFT JOIN C2 R ON S.user1 =R.user1 AND S.user2 =R.user2 WHERE R.income>0.30       

Because $lookup only supports single field condition, multi field join needs to use $unwind to split the data, then use $project: to get the field, and spell the join condition to the filter condition, the whole process is very tedious.

If we use esProc to assist MongoDB, this would be very simple. The key code is only one line (simpler than SQL):

	A
1	=mongo_open("mongodb://127.0.0.1:27017/test")
2	=mongo_shell(A1,"c1.find()").fetch()
3	=mongo_shell(A1,"c2.find()").fetch()
4	=A2.join(user1:user2,A3:user1:user2,output).select(income>0.30)
5	>A1.close()

 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 SPL is not only helpful to solve the join, but also to many difficult calculations of MongoDB. You can refer to SPL assists MongoDB calculation.

It's also easy to embed SPL into Java applications，please refer to How to Call an SPL Script in Java   For specific usage, please refer to   Getting started with esProc