How do I perform the SQL Join equivalent in MongoDB?

Question

How do I perform the SQL Join equivalent in MongoDB?

For example say you have two collections (users and comments) and I want to pull all the comments with pid=444 along with the user info for each.

comments 

{ uid:12345, pid:444, comment="blah" } 

{ uid:12345, pid:888, comment="asdf" } 

{ uid:99999, pid:444, comment="qwer" }

 users 

{ uid:12345, name:"john" } 

{ uid:99999, name:"mia" }

Is there a way to pull all the comments with a certain field (eg. ...find({pid:444}) ) and the user information associated with each comment in one go?

At the moment, I am first getting the comments which match my criteria, then figuring out all the uid's in that result set, getting the user objects, and merging them with the comment's results. Seems like I am doing it wrong.

Answer 1

You can use the $lookup operator to perform the SQL Join equivalent in MongoDB and the aggregation pipeline is essentially identical to a left outer join:

Here’s the query:-

{ 

  $lookup: 

    { 

      from: <collection to join>, 

      localField: <field from the input documents>, 

      foreignField: <field from the documents of the "from"  

      collection>, 

      as: <output array field> 

    } 

 }