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 https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup/#perform-an-uncorrelated-subquery-with--lookup — ChanOnly123, Aug 16 '22 at 09:41

score 75 · Accepted Answer · answered Apr 27 '17 at 09:20

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 — Venkatesh Somu, Aug 22 '18 at 07:19

score 60 · Answer 2 · edited Nov 02 '21 at 05:20

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 02 '21 at 05:20

Iain J. Reid

978
1
11
23

answered Jun 28 '11 at 06:53

Charles Hooper

2,739
1
19
16

1

How would one go about writing two queries for this? – Tri Nguyen Jan 05 '14 at 19:01
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 '14 at 00:10
1

How could this be achieved with plain old queries in MongoJS? – Pille Dec 08 '15 at 13:30
2

You can now use `$lookup` to join – Corbfon Apr 15 '17 at 00:34
8

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 '17 at 17:31
An important point to highlight here is that DBRefs are implemented by client side drivers which implies multiple fetch calls. As suggested above, use `$lookup` to avoid multiple network trips. – dev Jul 03 '21 at 00:31
How can i delete user `12345` from `users` collection and all posts created by the user at once. – Rahul Oct 17 '21 at 08:51

score 32 · Answer 3 · edited Dec 01 '16 at 15:29

32

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 01 '16 at 15:29

Kyr

5,383
2
27
22

answered Oct 22 '16 at 04:50

Anish Agarwal

1,793
19
19

What this does? Is this a query or does it add that metadata to the collection? – ViniciusPires Jan 09 '17 at 19:02
1

The $lookup operator is new since version 3.2 and is like a left outer join. See https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/ – almoraleslopez Feb 03 '17 at 08:53
Hi, can you please let me know what does filter do here in the first query? – Muhammad Usama Mashkoor Apr 27 '22 at 18:29

score 11 · Answer 4 · answered Nov 13 '13 at 12:57

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

score 4 · Answer 5 · answered Jun 28 '11 at 06:53

4

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

answered Jun 28 '11 at 06:53

score 3 · Answer 6 · answered Jun 28 '11 at 06:53

3

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

MongoDB query multiple collections at once

7 Answers7

Linked

Related