Node Js MongoDB Join Query
Introduction
MongoDB is a NoSQL database and there are no joins in NoSQL. But MongoDB provides few ways to use joins. There are several joins in SQL but the most frequently used is the inner join. In this article, we will discuss how to perform an inner join operation in MongoDB. But first, we will discuss what is an inner join. We will be using Node Js with MongoDB for these join query equivalents.
Suppose, we have two collections – A and B. If we apply a condition that matches value(s) in both these collections, the operation is known as inner join operation. As mentioned, there are no joins in MongoDB, so it can be tough to understand this at first. So be careful throughout the article.
Understanding collections
We need two collections. The name of the first collections is “barcelona”. I had omitted the _id field for better clarity.
1 2 3 4 5 6 7 8 9 10 11 12 | { "playername" : "Gerard Pique", "country" : "Spain" } { "playername" : "Sergio Busquets", "country" : "Spain" } { "playername" : "Luis Suarez", "country" : "Uruguay" } { "playername" : "Arthur", "country" : "Brazil" } { "playername" : "Sergi Roberto", "country" : "Spain" } { "playername" : "Ousmane Dembele", "country" : "France" } { "playername" : "Antoine Griezmann", "country" : "France" } { "playername" : "Jordi Alba", "country" : "Spain" } { "playername" : "Junior Firpo", "country" : "Spain" } { "playername" : "Marc-Andre ter Stegen", "country" : "Germany" } { "playername" : "Leonel Messi", "country" : "Argentina" } { "playername" : "Frenkie De Jong", "country" : "Netherlands" } |
As we can see, this collection is about the football players of the Spanish football club Barcelona. Each document has two fields – playername and country. The name of the second collection is “worldXI”.
1 2 3 4 5 6 7 8 9 10 11 | { "playername" : "Leonel Messi", "club" : "Barcelona", "country" : "Argentina" } { "playername" : "Cristiano Ronaldo", "club" : "Juventis", "country" : "Portugal" } { "playername" : "Robert Lewandowski", "club" : "Beyern Munich", "country" : "Poland" } { "playername" : "Sergio Busquets", "club" : "Barcelona", "country" : "Spain" } { "playername" : "Frenkie De Jong", "club" : "Barcelona", "country" : "Netherlands" } { "playername" : "Kevin De Bruyne", "club" : "Manchester City", "country" : "Belgium" } { "playername" : "Sergio Ramos", "club" : "Real Madrid", "country" : "Spain" } { "playername" : "Virgil van Dijk", "club" : "Liverpool", "country" : "Netherlands" } { "playername" : "Marcelo", "club" : "Real Madrid", "country" : "Brazil" } { "playername" : "Trent Alexander-Arnold", "club" : "Liverpool", "country" : "England" } { "playername" : "Marc-Andre ter Stegen", "club" : "Barcelona", "country" : "Germany" } |
The worldXI collection contains eleven documents. This is the best world XI according to me. Never mind LOL! So there are three fields in each document – playername, club, and country. When we perform a join operation, we need fields that are common in both the collections. In our case, two fields are common in both the collections – playername and country. To make sense, we will join these collections with the playername fields.
So the result will contain all the details of Barcelona football players that are present in the world XI team. Hope you understand what we are going to do.
inner join
As mentioned earlier, there are no official joins in MongoDB. But there are ways to do such joining in MongoDB. This is a bit complicated. We have to the $loopup of the aggregation pipeline. The $lookup takes a document as its value. There are four major fields in this document.
from : The name of the collection which will be joined.
localField and foreignField: The $lookup operation requires a field from both the collections that will join them. The localField is from the collection that in invoking the $lookup while the foreignField is from the second collection.
as: The connection between two collections in the result.
So let’s join the barcelona and worldXI collections.
1 | db.barcelona.aggregate({ $lookup: {from : "worldXI", localField: "playername", foreignField: "playername", as : "barcaInWorldXI"}}) |
According to this query, the collections, barcelona, and worldXI will join where the values of the field, playername in both the collections in same. Look at the field named “as”.
1 | as: "barcaInWorldXI"; |
This means, the documents that will be joined from worldXI with barcelona, their data will store in a new field, “barcaInWorldXI”.
So let’s see the result.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | > db.barcelona.aggregate({ $lookup: {from : "worldXI", localField: "playername", foreignField: "playername", as : "barcaInWorldXI"}}).pretty() { "_id" : ObjectId("5e1b3b8c2e6cf2f7f45585d7"), "playername" : "Gerard Pique", "country" : "Spain", "barcaInWorldXI" : [ ] } { "_id" : ObjectId("5e1b3ba72e6cf2f7f45585d8"), "playername" : "Sergio Busquets", "country" : "Spain", "barcaInWorldXI" : [ { "_id" : ObjectId("5e1b3e1b2e6cf2f7f45585e3"), "playername" : "Sergio Busquets", "club" : "Barcelona", "country" : "Spain" } ] } { "_id" : ObjectId("5e1b3bd52e6cf2f7f45585d9"), "playername" : "Luis Suarez", "country" : "Uruguay", "barcaInWorldXI" : [ ] } { "_id" : ObjectId("5e1b3be42e6cf2f7f45585da"), "playername" : "Arthur", "country" : "Brazil", "barcaInWorldXI" : [ ] } { "_id" : ObjectId("5e1b3c012e6cf2f7f45585db"), "playername" : "Sergi Roberto", "country" : "Spain", "barcaInWorldXI" : [ ] } { "_id" : ObjectId("5e1b3c1d2e6cf2f7f45585dc"), "playername" : "Ousmane Dembele", "country" : "France", "barcaInWorldXI" : [ ] } { "_id" : ObjectId("5e1b3c332e6cf2f7f45585dd"), "playername" : "Antoine Griezmann", "country" : "France", "barcaInWorldXI" : [ ] } { "_id" : ObjectId("5e1b3c3d2e6cf2f7f45585de"), "playername" : "Jordi Alba", "country" : "Spain", "barcaInWorldXI" : [ ] } { "_id" : ObjectId("5e1b3c502e6cf2f7f45585df"), "playername" : "Junior Firpo", "country" : "Spain", "barcaInWorldXI" : [ ] } { "_id" : ObjectId("5e1b3f962e6cf2f7f45585eb"), "playername" : "Marc-Andre ter Stegen", "country" : "Germany", "barcaInWorldXI" : [ { "_id" : ObjectId("5e1b3f7b2e6cf2f7f45585ea"), "playername" : "Marc-Andre ter Stegen", "club" : "Barcelona", "country" : "Germany" } ] } { "_id" : ObjectId("5e1b41412e6cf2f7f45585ec"), "playername" : "Leonel Messi", "country" : "Argentina", "barcaInWorldXI" : [ { "_id" : ObjectId("5e1b3d4d2e6cf2f7f45585e0"), "playername" : "Leonel Messi", "club" : "Barcelona", "country" : "Argentina" } ] } { "_id" : ObjectId("5e1b51f42e6cf2f7f45585ed"), "playername" : "Frenkie De Jong", "country" : "Netherlands", "barcaInWorldXI" : [ { "_id" : ObjectId("5e1b3e482e6cf2f7f45585e4"), "playername" : "Frenkie De Jong", "club" : "Barcelona", "country" : "Netherlands" } ] } > |
Check the result. We can see, some of the documents are joined with those from worldXI collection, but, the result also contains those documents from the barcelona collection that does not match the documents of the worldXI collection. This is not how the inner join should work, right? We only need documents in the result that are matched with the worldXI collection. So to make it a proper inned join, we need to use $match operator.
$match
We will use the $match operator to enhance our results. We will use it to remove all those documents that do not match with those of the worldXI collection. So how will we do it? Check the result again. Those documents who do not match have no content in the barcaInWorldXI field. It is an empty array. So we will display only those documents where the length of this array is greater than 0.
1 | { $match : { "barcaInWorldXI" : { $ne : []}}} |
We have to use the $ne operator to remove all the documents where the barcaInWorldXI is an empty array.
Let’s add this to the query we created and see the result.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | > db.barcelona.aggregate({ $lookup: {from : "worldXI", localField: "playername", foreignField: "playername", as : "barcaInWorldXI"}},{ $match : { "barcaInWorldXI" : { $ne : []}}} ).pretty() { "_id" : ObjectId("5e1b3ba72e6cf2f7f45585d8"), "playername" : "Sergio Busquets", "country" : "Spain", "barcaInWorldXI" : [ { "_id" : ObjectId("5e1b3e1b2e6cf2f7f45585e3"), "playername" : "Sergio Busquets", "club" : "Barcelona", "country" : "Spain" } ] } { "_id" : ObjectId("5e1b3f962e6cf2f7f45585eb"), "playername" : "Marc-Andre ter Stegen", "country" : "Germany", "barcaInWorldXI" : [ { "_id" : ObjectId("5e1b3f7b2e6cf2f7f45585ea"), "playername" : "Marc-Andre ter Stegen", "club" : "Barcelona", "country" : "Germany" } ] } { "_id" : ObjectId("5e1b41412e6cf2f7f45585ec"), "playername" : "Leonel Messi", "country" : "Argentina", "barcaInWorldXI" : [ { "_id" : ObjectId("5e1b3d4d2e6cf2f7f45585e0"), "playername" : "Leonel Messi", "club" : "Barcelona", "country" : "Argentina" } ] } { "_id" : ObjectId("5e1b51f42e6cf2f7f45585ed"), "playername" : "Frenkie De Jong", "country" : "Netherlands", "barcaInWorldXI" : [ { "_id" : ObjectId("5e1b3e482e6cf2f7f45585e4"), "playername" : "Frenkie De Jong", "club" : "Barcelona", "country" : "Netherlands" } ] } > |
Yes! It works perfectly.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started