Search in multiple collections mongoose

Question

I have about ~15 collections (different providers with different structures of data) that got a few fields in common, let's say title, description and price.

I'm currently trying to implement a search functionality for my API using the common fields and I am able to do it for every collection individually.

Is it possible to make a query for all 15 collections at once using that common fields? The problem of doing them one by one is the performance issues ( I have to lean my results ) and the fact that there is pagination on top of it.

I was thinking of creating a shared collection with common fields is a little bit late I guess.

Possible duplicate of [How do I perform the SQL Join equivalent in MongoDB?](https://stackoverflow.com/questions/2350495/how-do-i-perform-the-sql-join-equivalent-in-mongodb) — mehta-rohan, Mar 12 '19 at 05:12
I would recommend using a different database for your purpose. Even though mongo have added search feature lately, searching on 15 collections will be too much imo. If you can reduce the collection to one, it might be relevant. — HIRA THAKUR, Mar 12 '19 at 05:39

score 4 · Accepted Answer · answered Mar 12 '19 at 05:15

There is no way to run a single query on multiple collections, what you can still do is, run all the queries parallely and wait for all he results to come, and then you can send the collective result in the response.

Code should look something like this:

var promises = [];
promises.push(Collection1.find({title : "title",desription : "description",...}).lean().exec());
promises.push(Collection1.find({title : "title",desription : "description",...}).lean().exec());
promises.push(Collection1.find({title : "title",desription : "description",...}).lean().exec());
promises.push(Collection1.find({title : "title",desription : "description",...}).lean().exec());
promises.push(Collection1.find({title : "title",desription : "description",...}).lean().exec());
promises.push(Collection1.find({title : "title",desription : "description",...}).lean().exec());
promises.push(Collection1.find({title : "title",desription : "description",...}).lean().exec());
promises.push(Collection1.find({title : "title",desription : "description",...}).lean().exec());
promises.push(Collection1.find({title : "title",desription : "description",...}).lean().exec());
promises.push(Collection1.find({title : "title",desription : "description",...}).lean().exec());
promises.push(Collection1.find({title : "title",desription : "description",...}).lean().exec());
promises.push(Collection1.find({title : "title",desription : "description",...}).lean().exec());
promises.push(Collection1.find({title : "title",desription : "description",...}).lean().exec());
promises.push(Collection1.find({title : "title",desription : "description",...}).lean().exec());
promises.push(Collection1.find({title : "title",desription : "description",...}).lean().exec());

Promise.all(promises).then(results=>{
    // results[0] will have docs of first query
    // results[1] will have docs of second query
    // and so on...

    // you can combine all the results here and send back in response
}).catch(err=>{
    //handle error here
})

I guess there is no way too. I just spend a few hours looking up the docs and didn't find anything. I've decided to make a common collection afterall — Oleg Nesterov, Mar 12 '19 at 20:51

score 1 · Answer 2 · answered Mar 12 '19 at 05:20

According to the mongo documentation, $lookup can join only one external collection.

What you could do is to combine userInfo and userRole in one collection, as provided example is based on relational DB schema. Mongo is noSQL database - and this require different approach for document management.

Please find below 2-step query, which combines userInfo with userRole - creating new temporary collection used in last query to display combined data. In last query there is an option to use $out and create new collection with merged data for later use.

create collection

db.sivaUser.insert(
{    
"_id" : ObjectId("5684f3c454b1fd6926c324fd"),
    "email" : "admin@gmail.com",
    "userId" : "AD",
    "userName" : "admin"
})

 //"userinfo"
db.sivaUserInfo.insert(
{
"_id" : ObjectId("56d82612b63f1c31cf906003"),
"userId" : "AD",
"phone" : "0000000000"
})

//"userrole"
db.sivaUserRole.insert(
{
"_id" : ObjectId("56d82612b63f1c31cf906003"),
"userId" : "AD",
"role" : "admin"
})

aggregate collection

db.sivaUserInfo.aggregate([
{$lookup:
    {
       from: "sivaUserRole",
       localField: "userId",
       foreignField: "userId",
       as: "userRole"
    }
},
{
    $unwind:"$userRole"
},
{
    $project:{
        "_id":1,
        "userId" : 1,
        "phone" : 1,
        "role" :"$userRole.role"
    }
},
{
    $out:"sivaUserTmp"
}
])

db.sivaUserTmp.aggregate([
{$lookup:
    {
       from: "sivaUser",
       localField: "userId",
       foreignField: "userId",
       as: "user"
    }
},
{
    $unwind:"$user"
},
{
    $project:{
        "_id":1,
        "userId" : 1,
        "phone" : 1,
        "role" :1,
        "email" : "$user.email",
        "userName" : "$user.userName"
    }
}
])

Search in multiple collections mongoose

2 Answers2

create collection

aggregate collection