MongoDB query multiple collections at once

Question

users
{
 "_id":"12345",
 "admin":1
},
{
 "_id":"123456789",
 "admin":0
}

posts
{
 "content":"Some content",
 "owner_id":"12345",
 "via":"facebook"
},
{
 "content":"Some other content",
 "owner_id":"123456789",
 "via":"facebook"
}

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
– ChanOnly123, Commented Aug 16, 2022 at 9:41

Shubham Verma · Accepted Answer · 2017-04-27 09:20:36Z

You can use $lookup ( multiple ) to get the records from multiple collections:

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

Iain J. Reid · Accepted Answer · 2021-11-02 05:20:14Z

60

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.

edited Nov 2, 2021 at 5:20

Iain J. Reid

1,0301 gold badge13 silver badges24 bronze badges

answered Jun 28, 2011 at 6:53

Charles Hooper

2,7791 gold badge20 silver badges16 bronze badges

7 Comments

Tri Nguyen Over a year ago

How would one go about writing two queries for this?

CommaToast Over a year ago

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.

Pille Over a year ago

How could this be achieved with plain old queries in MongoJS?

Corbfon Over a year ago

You can now use $lookup to join

eggmatters Over a year ago

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.

|

Kyr · Accepted Answer · 2016-12-01 15:29:39Z

31

Here is answer for your question.

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"}
    }}
])

edited Dec 1, 2016 at 15:29

Kyr

5,5812 gold badges29 silver badges23 bronze badges

answered Oct 22, 2016 at 4:50

Anish Agarwal

1,82120 silver badges19 bronze badges

3 Comments

ViniciusPires Over a year ago

What this does? Is this a query or does it add that metadata to the collection?

almoraleslopez Over a year ago

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

Muhammad Usama Mashkoor Over a year ago

Hi, can you please let me know what does filter do here in the first query?

almoraleslopez · Accepted Answer · 2013-11-13 12:57:56Z

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 });

user2665694 · Accepted Answer · 2011-06-28 06:53:04Z

4

Perform multiple queries or use embedded documents or look at "database references".

answered Jun 28, 2011 at 6:53

user2665694

Comments

kheya · Accepted Answer · 2011-06-28 06:53:36Z

3

One solution: add isAdmin: 0/1 flag to your post collection document.

Comments

dleblanc · Accepted Answer · 2022-03-17 17:06:13Z

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

answered Mar 17, 2022 at 17:06

dleblanc

112 bronze badges

Collectives™ on Stack Overflow

MongoDB query multiple collections at once

7 Answers 7

2 Comments

7 Comments

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

7 Comments

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related