How to search one mongoose collection with some conditions on another collection?

Question

I have two mongoose collection. Let's consider collection-1 and collection-2. They have data similar to following data

collection-1 :

{'name': 'Tom', 'age':20, 'bank-acc':1111}, 
{'name': 'Dick', 'age':25, 'bank-acc':2222},
{'name': 'Hary', 'age':22, 'bank-acc':3333}

Collection-2

{'bank-acc':1111, 'balance':100},
{'bank-acc':2222, 'balance':200},
{'bank-acc':3333, 'balance':300}

Now I want to query these both collections at the same time to get the names from collection-1 whose age is grater than 20 and have balance grater than 100 in collection-2. Some people suggested me that because this is MongoDB which is a NO-SQL database so I should combine these two tables into one and then try to query it but in my case, it's highly infeasible for me to combine these two tables with the real schema. I guess it could be possible using aggregate queries using lookup or similar keywords may be but I have searched in many places I have no idea what to search while finding the answer to this question. Can anybody give a sample aggregate query to search two tables at once?

this is not the same question as of https://stackoverflow.com/questions/36459983/aggregation-filter-after-lookup because my question is asking about querying two collections in one query for the single collective result while the other mentioned question has a similar answer but the question is completely different — Point Networks, Jun 03 '18 at 12:30

score 2 · Answer 1 · answered May 30 '18 at 09:22

use $lookup:

$lookup New in version 3.2.

Performs a left outer join to an unsharded collection in the same database to filter in documents from the “joined” collection for processing. To each input document, the $lookup stage adds a new array field whose elements are the matching documents from the “joined” collection. The $lookup stage passes these reshaped documents to the next stage.

Try something like this:

db.getCollection('collection1').aggregate([
    {   $lookup:
            {
            from: "collection2",
            localField: "bank-acc",
            foreignField: "bank-acc",
            as: "collection2_doc"
            }
    },
        {
            "$unwind": {
              path: "$collection2_doc",
              preserveNullAndEmptyArrays: false
            }
        },

        {
        $match : {
          name: "Tom", //field from collection1
          "collection2_doc.ballance": {$gt: 100} //field from collection2
          }
    }
]);

Salman Rifai · Answer 2 · 2018-05-30T09:31:36.467

collection1

{'name': 'Tom', 'age':20, 'bank-acc':1111},   
{'name': 'Dick', 'age':25, 'bank-acc':2222},  
{'name': 'Hary', 'age':22, 'bank-acc':3333}

collection2

{'bank-acc':1111, 'balance':100},
{'bank-acc':2222, 'balance':200},  
{'bank-acc':3333, 'balance':300}  


 db.getCollection('collection1').aggregate([
    {
        $match:{age:{$gt:20} }
    },
    {
        $lookup:{
           from:'collection2',
           localField:"bank-acc",
           foreignField:'bank-acc',
           as:'balance'
         }
    },
    {
         $unwind:'$balance'
    },
    {
         $match:{
             'balance.balance':{$gt:200}
         }
    },
    {
         $group:{
             _id:null,
             names:{$push:'$name'}
         }
    }
])

score 1 · Answer 3 · answered May 30 '18 at 09:23

You can use $lookup on aggregation function to query on multiple collection:

db.getCollection('collection1').aggregate([{
        $lookup:{
            from:"collection2",
            localField:"name",
            foreignField:"bank-acc",
            as:"bank"
        }
    },
    {
        $match:{'bank-acc' : 1111.0}
    }])

How to search one mongoose collection with some conditions on another collection?

3 Answers3