”;
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 −
Sr.No | Parameter & Description |
---|---|
1 |
from
Specifies the collection in the same database to perform the join with.
from is optional, you can use a $documents stage in a $lookup stage instead. For an example, see Use a $documents Stage in a $lookup Stage. |
2 |
localField
Specifies the field from the documents input to the $lookup stage. $lookup performs an equality match on the localField to the foreignField from the documents of the from collection. |
3 |
foreignField
Specifies the field from the documents in the from collection. |
4 |
As
Specifies the name of the new array field to add to the input documents. The new array field contains the matching documents from the from collection. |
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}]}
”;