Node.js - MongoDB Join

---

---

MongoDB is a NoSQL database, and it doesn’t support JOIN operations as found in relation databases such as MySQL. However, a similar functionality can be achieved by calling the aggregate() method of the Collection object, and the $lookup stage.

$aggregate() function

This function Performs a left outer join to another collection in the same database to filter in documents from the "joined" collection for processing.

$lookup: Performs a left outer join to a collection in the same database to filter in documents from the "joined" collection for processing. The $lookup stage adds a new array field to each input document. The new array field contains the matching documents from the "joined" collection.

To perform an equality match between a field from the input documents with a field from the documents of the "joined" collection, the $lookup stage has this syntax −

{
   $lookup:
      {
         from: <collection to join>,
         localField: <field from the input documents>,
         foreignField: <field from the documents of the "from" collection>,
         as: <output array field>
      }
}

The parameters in $lookup stage are as follows −

The $lookup operation corresponds to the following SQL query −

SELECT *, <output array field>
FROM collection
WHERE <output array field> IN (
   SELECT *
   FROM <collection to join>
   WHERE <foreignField> = <collection.localField>
);

Example

To demonstrate the JOIN operation in MongoDB, create two Collections − inventory and orders.

The inventory Collection

( [
   { prodId: 100, price: 20, quantity: 125 },
   { prodId: 101, price: 10, quantity: 234 },
   { prodId: 102, price: 15, quantity: 432 },
   { prodId: 103, price: 17, quantity: 320 }
] )

The orders collection

( [
   { orderId: 201, custid: 301, prodId: 100, numPurchased: 20 },
   { orderId: 202, custid: 302, prodId: 101, numPurchased: 10 },
   { orderId: 203, custid: 303, prodId: 102, numPurchased: 5 },
   { orderId: 204, custid: 303, prodId: 103, numPurchased: 15 },
   { orderId: 205, custid: 303, prodId: 103, numPurchased: 20 },
   { orderId: 206, custid: 302, prodId: 102, numPurchased: 1 },
   { orderId: 207, custid: 302, prodId: 101, numPurchased: 5 },
   { orderId: 208, custid: 301, prodId: 100, numPurchased: 10 },
   { orderId: 209, custid: 303, prodId: 103, numPurchased: 30 }
] )

The following code calls aggregate() method on the Collection object and the $lookup stage.

const {MongoClient} = require('mongodb');

async function main(){

   const uri = "mongodb://localhost:27017/";
   const client = new MongoClient(uri);

   try {
      await client.connect();
      await joindocs(client, "mydb", "orders", "inventory");
   } finally {
      await client.close();
   }
}

main().catch(console.error);

async function joindocs(client, dbname, col1, col2){
   const result = await client.db(dbname).collection('orders').aggregate([
   { $lookup:
      {
         from: 'inventory',
         localField: 'prodId',
         foreignField: 'prodId',
         as: 'orderdetails'
      }
   }
   ]).toArray();
   result.forEach(element => {
      console.log(JSON.stringify(element));
   });
}

The $lookup stage lets you specify which collection you want to join with the current collection, and which fields that should match.

Output

{"_id":"658c4b14943e7a1349678bf3","orderId":201,"custid":301,"prodId":100,"numPurchased":20,"orderdetails":[{"_id":"658c4aff943e7a1349678bef","prodId":100,"price":20,"quantity":125}]}
{"_id":"658c4b14943e7a1349678bf4","orderId":202,"custid":302,"prodId":101,"numPurchased":10,"orderdetails":[{"_id":"658c4aff943e7a1349678bf0","prodId":101,"price":10,"quantity":234}]}
{"_id":"658c4b14943e7a1349678bf5","orderId":203,"custid":303,"prodId":102,"numPurchased":5,"orderdetails":[{"_id":"658c4aff943e7a1349678bf1","prodId":102,"price":15,"quantity":432}]}
{"_id":"658c4b14943e7a1349678bf6","orderId":204,"custid":303,"prodId":103,"numPurchased":15,"orderdetails":[{"_id":"658c4aff943e7a1349678bf2","prodId":103,"price":17,"quantity":320}]}
{"_id":"658c4b14943e7a1349678bf7","orderId":205,"custid":303,"prodId":103,"numPurchased":20,"orderdetails":[{"_id":"658c4aff943e7a1349678bf2","prodId":103,"price":17,"quantity":320}]}
{"_id":"658c4b14943e7a1349678bf8","orderId":206,"custid":302,"prodId":102,"numPurchased":1,"orderdetails":[{"_id":"658c4aff943e7a1349678bf1","prodId":102,"price":15,"quantity":432}]}
{"_id":"658c4b14943e7a1349678bf9","orderId":207,"custid":302,"prodId":101,"numPurchased":5,"orderdetails":[{"_id":"658c4aff943e7a1349678bf0","prodId":101,"price":10,"quantity":234}]}
{"_id":"658c4b14943e7a1349678bfa","orderId":208,"custid":301,"prodId":100,"numPurchased":10,"orderdetails":[{"_id":"658c4aff943e7a1349678bef","prodId":100,"price":20,"quantity":125}]}
{"_id":"658c4b14943e7a1349678bfb","orderId":209,"custid":303,"prodId":103,"numPurchased":30,"orderdetails":[{"_id":"658c4aff943e7a1349678bf2","prodId":103,"price":17,"quantity":320}]}