MongoDB query multiple collections at once

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.

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

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