join - MongoDB query multiple collections at once

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

Here is a sample from my mongodb. I want to get all the posts which has "via" attribute equal to "facebook" and posted by an admin ("admin":1). I couldn't figure out how to acquire this query. Since mongodb is not a relational database, I couldn't do a join operation. What could be the solution ?

Perform an Uncorrelated Subquery with $lookup mongodb.com/docs/manual/reference/operator/aggregation/lookup/… – ChanOnly123 Aug 16, 2022 at 9:41

Example:

If you have more collections ( I have 3 collections for demo here, you can have more than 3 ). and I want to get the data from 3 collections in single object:

The collection are as:

db.doc1.find().pretty();

"_id" : ObjectId("5901a4c63541b7d5d3293766"), "firstName" : "shubham", "lastName" : "verma"

db.doc2.find().pretty();

"_id" : ObjectId("5901a5f83541b7d5d3293768"), "userId" : ObjectId("5901a4c63541b7d5d3293766"), "address" : "Gurgaon", "mob" : "9876543211"

db.doc3.find().pretty();

"_id" : ObjectId("5901b0f6d318b072ceea44fb"), "userId" : ObjectId("5901a4c63541b7d5d3293766"), "fbURLs" : "http://www.facebook.com", "twitterURLs" : "http://www.twitter.com"

Now your query will be as below:

db.doc1.aggregate([
    { $match: { _id: ObjectId("5901a4c63541b7d5d3293766") } },
        $lookup:
            from: "doc2",
            localField: "_id",
            foreignField: "userId",
            as: "address"
        $unwind: "$address"
        $project: {
            __v: 0,
            "address.__v": 0,
            "address._id": 0,
            "address.userId": 0,
            "address.mob": 0
        $lookup:
            from: "doc3",
            localField: "_id",
            foreignField: "userId",
            as: "social"
        $unwind: "$social"
    $project: {      
           __v: 0,      
           "social.__v": 0,      
           "social._id": 0,      
           "social.userId": 0
]).pretty();
Then Your result will be:
    "_id" : ObjectId("5901a4c63541b7d5d3293766"),
    "firstName" : "shubham",
    "lastName" : "verma",
    "address" : {
        "address" : "Gurgaon"
    "social" : {
        "fbURLs" : "http://www.facebook.com",
        "twitterURLs" : "http://www.twitter.com"
If you want all records from each collections then you should remove below line from query:
            $project: {
                __v: 0,
                "address.__v": 0,
                "address._id": 0,
                "address.userId": 0,
                "address.mob": 0
        $project: {      
               "social.__v": 0,      
               "social._id": 0,      
               "social.userId": 0
After removing above code you will get total record as:
    "_id" : ObjectId("5901a4c63541b7d5d3293766"),
    "firstName" : "shubham",
    "lastName" : "verma",
    "address" : {
        "_id" : ObjectId("5901a5f83541b7d5d3293768"),
        "userId" : ObjectId("5901a4c63541b7d5d3293766"),
        "address" : "Gurgaon",
        "mob" : "9876543211"
    "social" : {
        "_id" : ObjectId("5901b0f6d318b072ceea44fb"),
        "userId" : ObjectId("5901a4c63541b7d5d3293766"),
        "fbURLs" : "http://www.facebook.com",
        "twitterURLs" : "http://www.twitter.com"
                Hi, @Shubham Verma, how to get the multiple records from "social" or "address" collections. in the same query. like I have multiple records in social collections for the single user
– Venkatesh Somu
                Aug 22, 2018 at 7:19
Trying to JOIN in MongoDB would defeat the purpose of using MongoDB. You could, however, use a DBref and write your application-level code (or library) so that it automatically fetches these references for you.
Or you could alter your schema and use embedded documents.
Your final choice is to leave things exactly the way they are now and do two queries.
                Doctrine MongoDB ODM is a decent library for handling such things. However it only lets you join through one layer of references; you can't populate references within references within references with any degree of syntactical ease.
– CommaToast
                Oct 31, 2014 at 0:10
                Sorry, but I just have to say, "Trying to use a DB without a concept of a JOIN defeats the purpose of databases. Entirely" Mongo added $lookup due to mistakenly assuming this not to be the case. Embedded documents are not the best answer as they are not synchronized with the records they should be equivalent to, and you wind up duplicating data.
– eggmatters
                Aug 30, 2017 at 17:31
db.getCollection('users').aggregate([
    {$match : {admin : 1}},
    {$lookup: {from: "posts",localField: "_id",foreignField: "owner_id",as: "posts"}},
    {$project : {
            posts : { $filter : {input : "$posts"  , as : "post", cond : { $eq : ['$$post.via' , 'facebook'] } } },
            admin : 1
Or either you can go with mongodb group option.
db.getCollection('users').aggregate([
    {$match : {admin : 1}},
    {$lookup: {from: "posts",localField: "_id",foreignField: "owner_id",as: "posts"}},
    {$unwind : "$posts"},
    {$match : {"posts.via":"facebook"}},
    { $group : {
            _id : "$_id",
            posts : {$push : "$posts"}
                The $lookup operator is new since version 3.2 and is like a left outer join. See docs.mongodb.com/manual/reference/operator/aggregation/lookup
– almoraleslopez
                Feb 3, 2017 at 8:53
As mentioned before in MongoDB you can't JOIN between collections.
For your example a solution could be:
var myCursor = db.users.find({admin:1});
var user_id = myCursor.hasNext() ? myCursor.next() : null;
db.posts.find({owner_id : user_id._id});
See the reference manual - cursors section: http://es.docs.mongodb.org/manual/core/cursors/
Other solution would be to embed users in posts collection, but I think for most web applications users collection need to be independent for security reasons. Users collection might have Roles, permissons, etc.
posts
 "content":"Some content",
 "user":{"_id":"12345", "admin":1},
 "via":"facebook"
 "content":"Some other content",
 "user":{"_id":"123456789", "admin":0},
 "via":"facebook"
and then:
db.posts.find({user.admin: 1 });
Posting since I wanted to flatten the merged documents, vs a tiered document that the other answers produce.
To merge multiple collections into a flat single document, look at Mongo docs for $lookup with $mergeObjects: https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#use--lookup-with--mergeobjects
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.