How to join collections in mongodb

Question

I have a collection where data is stored by a owner and owner id is also stored along with data. On other hand there is another collection in which owner details are stored.

{
    "_id" : ObjectId("58103aac9899ff238cbae413"),
    "id" : 10585131,
    "title" : "Edfwef EdfwefE dfwefE dfwefEd fwef",
    "category" : "Furniture",
    "condition" : "Brand New",
    "brand" : "Samsung",
    "price" : "3434",
    "description" : "sdvsdfv",
    "date" : ISODate("2016-10-26T05:10:04.240Z"),
    "ownerId" : "40903aac9899er238cbae598",
    "__v" : 0
}

Now I am fetching above data and showing on a HTML file . On same html file where data are fetching, I want to show owner details on the basis of ownerId. I mean how to join two collections in mongodb? Please help.

Thanks.

Updated:

admodel

`var adSchema=new Schema({ id:{type:Number}, title:{type:String, required:true}, category:{type:String}, condition:{type:String}, brand:{type:String}, price:{type:String}, city:{type:String}, town:{type:String}, description:{type:String}, image:{type:String}, date:{type:Date}, ownerId:{type:Schema.Types.ObjectId, ref: 'user'} }); module.exports=mongoose.model('ads', adSchema);`

user

`var UserSchema=new Schema({ name:{type:String, require:true}, password:{type:String, require:true, unique:true,}, email:{type:String, require:true, unique:true,}, mobile:{type:String} }); module.exports = mongoose.model('User', UserSchema);`

Inserting Data

`apiRoutes.post('/adcreate',upload ,function(req, res, next){
  var token=req.headers.authorization;
  var owner=jwt.decode(token, config.secret);
  var currentDate=Date.now();
  var adId=Math.floor(Math.random()*13030978)+1;
  var ad=new admodel({
          id:adId,
          title:  req.body.title,
          category:req.body.category,
          condition:req.body.condition,
          brand:req.body.brand,
          price:req.body.price,
          city:req.body.city,
          town:req.body.town,
          description:req.body.description,
          date:currentDate,
          ownerId:owner.id
      }) ;
    ad.save(function(err, data){
          if(err){
            return res.json({success:false, msg:'Ad not Posted'});
          }else{
            res.json({succes:true, msg:'Successfully ad Posted'});
          }
     });
});`

Fetching Data

`apiRoutes.get('/individualads/:id', function(req, res,next){ admodel.findOne({_id:req.params.id}).populate('ownerId').exe‌c(function(err, data){ if(err){ res.json(err); } else{ console.log(data); res.json(data); } }); });`

This is a duplicate of about 1000 other questions on this tag, for example http://stackoverflow.com/questions/2350495/how-do-i-perform-the-sql-join-equivalent-in-mongodb you could use the aggregation framework lookup but I am unsure about the performance of it — Sammaye, Nov 07 '16 at 09:52
I am not able to understand your suggested link. Can you please elaborate with my data? — Saurabh Sharma, Nov 07 '16 at 09:54

Aruna · Accepted Answer · 2016-11-07T10:16:07.970

2

You can try as below and replace the properties as per your db collection

Reference Link: https://docs.mongodb.com/v3.2/reference/operator/aggregation/lookup/

// I assume 'orders' is your first collection name
    db.orders.aggregate([
        {
          $lookup:
            {
              from: "owners",    // your second collection name
              localField: "ownerId",  // matched property in first collection
              foreignField: "_id",    // matched property to join to the second collection
              as: "owner"       // owner details output property
            }
       }
    ])

Using mongoose as you requested,

On your admodel, add ref with the target (second) model name as below

ownerId: {
            type: Schema.Types.ObjectId,
            ref: 'owner'  // This should be a name of the second model name in which you want to join and fetch data
        }

Then change the route as below,

apiRoutes.get('/individualads/:id', function(req, res,next){
   admodel.findOne({_id:req.params.id}).populate('ownerId').exec(function(err, data){ 
      if(err){
        res.json(err);
      } else{ 
        console.log(data); 
        res.json(data); 
      } 
   }); 
});

edited Nov 07 '16 at 10:16

answered Nov 07 '16 at 09:54

Aruna

11,959
3
28
42

where you required this, since you haven't provided those info. If you need to return these data to the client, then put the same on the corresponding route handler – Aruna Nov 07 '16 at 10:02
If you are new to mongodb and not sure how to use this with nodejs then please refer this link, `https://www.terlici.com/2015/04/03/mongodb-node-express.html` – Aruna Nov 07 '16 at 10:05
Suppose I want to merge collections in below route `apiRoutes.get('/individualads/:id', function(req, res,next){ admodel.findOne({_id:req.params.id}, function(err, data){ if(err){res.json(err);} else{ console.log(data); res.json(data); } }); });` how to do it now? – Saurabh Sharma Nov 07 '16 at 10:05
I tried, There is nothing goes in `ownerId`. my both models are in separated files. thats why its not inserting or any other reason for that? – Saurabh Sharma Nov 07 '16 at 10:31
which one fails, insert or fetch? – Aruna Nov 07 '16 at 10:56
As it's coming as a string `ownerId:owner.id` its not inserting there. You should convert it like, `ownerId:mongoose.Types.ObjectId(owner.id)` – Aruna Nov 07 '16 at 11:58
can you try this? – Aruna Nov 07 '16 at 11:59
Great Now `ownerid` is inserting. But now while fetching data its consoling an error `MissingSchema Error: Schema hasn't been registered for model "user"` – Saurabh Sharma Nov 07 '16 at 12:03
okay, I got it, change `ref: 'user'` to `ref: 'User'`. See the pascal case on the model name – Aruna Nov 07 '16 at 12:24
great... It works.... but in `ownerid` its not inserting loggedin user's id its storing next id of inserted data ... data is given below `{ "_id" : ObjectId("58206cc994b3f134a010510e"), "id" : 4159581, "title" : "vnsdfsdf", "condition" : "Brand New", "price" : "099", "category" : "Furniture", "description" : "sdfsdfsdfsdf", "date" : ISODate("2016-11-07T12:00:09.738Z"), "ownerId" : ObjectId("58206cc994b3f134a010510d"), "__v" : 0 }` – Saurabh Sharma Nov 07 '16 at 12:37
You can see owner id is next of ad id... its not actual id of owner. – Saurabh Sharma Nov 07 '16 at 12:38
what is the value of `owner.id` in `ownerId:mongoose.Types.ObjectId(owner.id)`? I have to check whether its valid objectId – Aruna Nov 07 '16 at 12:41
`ownerId:mongoose.Types.ObjectId(owner.id)` is generating new value... and that not right. – Saurabh Sharma Nov 07 '16 at 12:44
If you pass valid objectId inside `mongoose.Types.ObjectId(owner.id)` then it will convert otherwise, it will generate new one. Usually, `ownerId:owner.id` will work if it's a valid object id. So its not valid object id. You need to print this to check whats the value of `owner.id` which you got from `var owner=jwt.decode(token, config.secret);`. It should be `24` chars string like `58206cc994b3f134a010510d`. You have to check why its not coming like that. – Aruna Nov 07 '16 at 12:48
I tried .... Its working great when I am used `ownerId:owner` instead of `ownerId:mongoose.Types.ObjectId(owner.id)` `ownerId:owner` is storing only id – Saurabh Sharma Nov 08 '16 at 07:35

score 1 · Answer 2 · answered Nov 07 '16 at 10:39

1

Can do like this:

 db.owner.find({}).populate({'path':'ownerId','model':'owner'}).exec()

answered Nov 07 '16 at 10:39

Simran

2,364
1
12
19

How to join collections in mongodb

2 Answers2