MongoDB: $lookup accross 2 databases

Question

I have two databases.

One database with user information.
One database with sales information.

I am trying to aggregate on a collection. I need to do a $lookup with the localField being an user ID in the user database.

How can I do this?

In mongoose I can use populate and specify the model I want to use, like this:

.populate({
  path: 'user',
  select: 'name',
  model: UserModelFromDifferentDB
})

But in the $lookup I can't see such an option?

Example where the issue starts:

        const data = await Sale.aggregate([
        {
            $match: {
                creator: Types.ObjectId("5d5154a37dcf4a00171c0018")
            }
        },
        {
            $lookup:
              {
                from: 'users', // no users collection in this DB, it is in the other db
                localField: 'user',
                foreignField: '_id',
                as: 'user'
              }
        },
      ])

users collection and sales collection are in seperate databases no in single database? — sushant mehta, Sep 04 '19 at 11:43
check https://stackoverflow.com/questions/39222798/is-it-possible-to-do-a-lookup-aggregation-between-two-databases-in-mongodb — sushant mehta, Sep 04 '19 at 11:59
They are in different dbs because I have one authentication/user db, and other dbs for each organization in my application. You can only access the organization db once you are authenticated through the user db. — vemund, Sep 04 '19 at 12:07
It is not possible to query using lookup in two different db's. $lookup in mongodb supports Performs a left outer join to an unsharded collection in the same database. alternative is given in above link. — sushant mehta, Sep 04 '19 at 12:09
Guess I'll just use Mongoose .find and populate then, and then literate over the result to project it the way I want. — vemund, Sep 04 '19 at 12:11

score 3 · Answer 1 · answered Sep 04 '19 at 12:37

As I know it is not possible with aggregate to do $lookup across two databases. But if you can do some logical code then "getSiblingDB()" will help you.

 first_DB_data = db.getSiblingDB('students').bca.find() //'students' is DB,'bca' is collection
 second_DB_data = db.getSiblingDB('marks').finals.find() //'marks' is DB,'finals' is collection

After getting all records from both DB you can perform code for getting your desired output. Now you have all records from both DB you can filter out data on basis of any condition.. Hope it will help you

MongoDB: $lookup accross 2 databases

1 Answers1