Pritesh     Aug,30 2017

Perform INNER JOIN in MongoDB using $lookup aggregation operator

In MongoDB 3.2 has introduced $lookup operator in aggregation framework which can be utilized to perform LEFT JOIN. Please note that MongoDB data operation is not like operating data in relational database so it is always advisable to have relevant data to be within the same document those often using together. But in a certain case, it is required to put in two different collections due to various like, to avoid data duplication for which we have heavy update operations, use in multiple collections etc. 

 

JOIN is one of the key difference between SQL and NoSQL database where as MongoDB Aggregation $lookup operator is helpful and powerful to get JOIN for two collections like doing it in RDBMS. 

MongoDB Aggregation

Let us see further:

Syntax: 

If you are coming from RDBMS world then you may be expecting this as separate document link all fields from the local and foreign collection but NO, all foreign documents will be added as an array of local collection document. Since all foreign document added inside the document you may want to use only if a number of a foreign relative documents are limited and not making size of result document to exceed document size limit.

Ex:

Let's say have two collections 'orders' and 'product' with following data.

Orders:

Products:

Now to retrieve order with product detail we can use below
 

Result documents:

Normally, order may contains multiple items, let's see how you can perform $lookup within array element.

Let's change orders collections to look like below

In this case order items are in array which required to unwind using $unwind operator which will create separate document per item and then you can perform lookup. Script may look like below.
 

Result:

You may not want to use $lookup operator to get all orders for a particular product in other word join orders on product. In case of best selling product we might have thousands order and all added within single array may cause document size limit array. 

 

Surely this will help to understand $lookup aggregation operator how and when to use it. Practice for this to use it effectively.