CODEWITHSUNDEEP
javascriptnode.jsmongodbmongoose
Kunal Dholiya
Kunal Dholiya

Reputation: 385

How to JOIN two collection by _id with where condition in Mongodb and NodeJS

I've two collections called user and subscription, every subscription has user_id which is _id of user collection. How can I join these two collections by where condition with is_account_active = 1.

Please check the below code which I'm using:

const users = await User.find({ is_account_active: 1 });

This will get me all users which have is_account_active flag as 1 but at the same time, I want subscription details also with respective user ids.

Upvotes: 0

Views: 2384

Answers (3)

Sean Miller
Sean Miller

Reputation: 53

I'm using Mongodb right now with Mathon's excellent answer. I don't have the reputation points to state this in the comments: I believe there is a stray period after the 'as' and the argument foreignKey should be foreignField - at least Mongodd 6.0.3 is presenting an error with it and NodeJS. It works for me with those changes as shown below.

const users = await User.aggregate([
  {
    $match: {
      is_account_active: 1
    }
  },
  {
    $project: {
      "_id": {
        "$toString": "$_id"
      }
    }
  },
  {
    $lookup: {
      from: 'subscriptions',     //collection name
      localField: '_id',
      foreignField: 'user_id',
      as: 'subscription'        //alias
    }
  }
]);

Upvotes: 0

Nilesh Solanki
Nilesh Solanki

Reputation: 63

You can below query.

const users = await User.aggregate([
  {
    $match: {
      your_condition
    }
  },
  {
    $lookup: {
      from: 'subscriptions', // secondary db
      localField: '_id',
      foreignKey: 'user_id',
      as: 'subscription' // output to be stored
    }
  }
]);

But instead of using _id as a foreign it should be better if you can use a new field like user_id in primary collection and can use auto increment on that which will now automatically insert new data with new unique id, and you can create index on it for faster query execution.

Upvotes: 1

mathon
mathon

Reputation: 79

You can use for example aggregate function. If you keep user_id as string and you have mongo db version >= 4.0 then you can make _id conversion to string (because _id is an ObjectId type):

const users = await User.aggregate([
  {
    $match: {
      is_account_active: 1
    }
  },
  {
    $project: {
      "_id": {
        "$toString": "$_id"
      }
    }
  },
  {
    $lookup: {
      from: 'subscriptions',     //collection name
      localField: '_id',
      foreignKey: 'user_id',
      as: 'subscription'.        //alias
    }
  }
]);

But it is a better idea to store user_id in Subscription schema as Object id

user_id: {
    type: mongoose.Schema.Types.ObjectId,
    ref:'User'
}

so then

const users = await User.aggregate([
  {
    $match: {
      is_account_active: 1
    }
  },
  {
    $lookup: {
      from: 'subscriptions',     //collection name
      localField: '_id',
      foreignKey: 'user_id',
      as: 'subscription'.        //alias
    }
  }
]);

More about ObjectId

More about Aggregate function

Upvotes: 0

Related Questions