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 ?

Answer 1

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