FULL outer-join using Mongodb

MongoDB University M121: The MongoDB Aggregation Framework
#1

How to achieve full outer join in MongoDB. Consider I have two collections, need to merge as a single collection using mongo query.

User Collection

{
     "_id" : 1,
     "name": "Pradeep P",
     "skll" : "Java"
}

{
     "_id" : 2,
     "name": "Umesh C",
     "skll" : "Angular"
}

Member Collection

{
     "_id" : 3,
     "name": "Shubham",
     "hobby" : "cricket"
}

{
     "_id" : 4,
     "name": "Raju",
     "hobby" : "Foddie"
}

As one collection with the result

{
     "_id" : 1,
     "name": "Pradeep P",
     "skll" : "Java"
}

{
     "_id" : 2,
     "name": "Umesh C",
     "skll" : "Angular"
}

{
     "_id" : 3,
     "name": "Shubham",
     "hobby" : "cricket"
}

{
     "_id" : 4,
     "name": "Raju",
     "hobby" : "Foddie"
}
#2

What you want to use is $lookup and you will find the documentation at https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/

In addition, if you want to store the result in a collection $out is the way to go. See https://docs.mongodb.com/manual/reference/operator/aggregation/out/

1 Like
#3

Just going to add that the example that you gave doesn’t fully represent the title of your post, i.e. Full Outer Join. It looks more like a Left Outer Join with an uncorrelated subquery (which is where @steevej-1495’s $lookup suggestion comes in), or a Union which isn’t currently supported in MongoDB.

To achieve a Full Outer Join, you either have to do it client-side or perform separate multiple aggregations (using $lookup) which when combined, will form the Full Outer Join; you can use a temporary collection for combining these combined results. Similar principles apply for a Union join.

Lastly, if you’re needing to perform a Full Outer Join on a regular basis, consider re-modelling your schemas.