Implement “Joins” in MongoDB equivalent to SQL

Since I am coming from a SQL background so writing queries in SQL where I join tables is quite simple but I guess I am missing that in Mongoose / Mongodb. In earlier version of MongoDB we can’t perform “joins” equivalent to SQL.

As of MongoDB 3.2 above statement is no longer correct. The new $lookup operator added to the aggregation pipeline is essentially identical to a left outer join.

From the docs:

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

For example I have a persons collection as:

{
       _id: <ObjectID>,
       name: <person name>,
       city: <person city>,
       parentId: <_id field of its parent person>
}

Here I’m storing person data with parent – child relation in persons collection. Now let create query on my persons collection which gives me data something like:

{
       _id: <ObjectID>,
       name: <person name>,
       city: <person city>,
       parentId: <_id field of its parent person>,
       parent: [{_id: <ObjectID>, name: <person name>, city: <person city>, parentId: <parent _id>}] // parent's document
}

To achieve above result, we are going to use $lookup introduce in MongoDB 3.2. $lookup performs a left outer join to an collection in the same database to filter in documents from the “joined” collection for processing. Note that the $lookup stage does an equality match between a field from the input documents with a field from the documents of the “joined” collection.

To each input document, the $lookup stage adds a new array field whose elements are the matching documents from the “joined” collection. The $lookup stage passes these reshaped documents to the next stage.

Let’s see how we can use $lookup in our case:

db.persons
        .aggregate([
            {$lookup: {
                from: "persons",
                localField: "parentId",
                foreignField: "_id",
                as: "parent"
            }}
        ])

But still I didn’t get empty parent array for each document. It’s strange for me for a moment. Sometime back I realize that in my person collection _id is type of “ObjectID”  while my parentId is type of “String“. And I notice that MongoDB $lookup docs already mentioned that “$lookup stage does an equality match between a field from the input documents with a field from the documents of the “joined” collection”.

In this case I simple update my parentId with type of ObjectID. You can also use ‘$proejct‘ aggregation and add new computed field which contains parentId of ObjectID type. And then create join with this new computed field that’s it.

It’s works well for me as I don’t have much data on my persons collection. But there may be performance issue with $lookup for large number of data not sure about it :). Let’s comment what’s your thought on $lookup. Thanks!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s