Mongoose: Filter collection based on values in another collection

Question

Consider documents that contain an arrays of ID of another collection, how can I find documents applying a filter based on the related collection using mongoose? I can't find my error

  Installation.aggregate(                        
                    [
                          // Stage 1
                          {
                                $lookup: {
                                      from: "users",
                                      localField: "_id",
                                      foreignField: "_id",
                                      as: "Users"
                                }
                          },

                          // Stage 2
                          {
                                $unwind: {
                                      path: "$Users"
                                }
                          },
                          // Stage 3
                          {
                                $match: 
                                      {"Users.Email1" : "[email protected]"}
                                
                          }, 
                          {
                                $sort: { _id: -1 }
                          },
                          {
                                $limit: 10
                          }                            
                    ]
              ,function (err, installations) {
                    
                   console.log(installations); //<<<<< Empty
                    /*
                    Installation.populate( 'Parent', '_id Email1','Company'), function(err,results) {
                          Installation.count(filter).exec(function (err, count) {
                                res.send({ success: true, results: installations, total: count });
                          });
                    };
                    */                                                             
              });

Adrian

it would be easier to answer if you put some sample data with your question – Catchall

Are you getting results till stage 3? – Movable

Put in values for filter, sorting, _count, req.params.page, req.param.count? – Movable

done my hero :-) – Adrian

Can you add `**console.log(err)** before console.log(installations);??? – Movable

Also, you cannot populate data returned from aggregate, you can add more operations of lookup for join and project to project the fields you want to, – Movable

the error is null (thank you for your patience again) – Adrian

it works ! localField was the error..thank you so much! for the populate I've create another post! – Adrian

Answer 1

You can do this using aggregate query:

db.collectionA.aggregate(

    // Pipeline
    [
        // Stage 1
        {
            $lookup: {
                from: "collectionB",
                localField: "_id",
                foreignField: "_id",
                as: "collectionBData"
            }
        },

        // Stage 2
        {
            $unwind: {
                path : "$collectionBData"
            }
        },

        // Stage 3
        {
            $match: {
                "collectionBData.email": "[email protected]"
            }
        },

    ]

);

Hope this solves your query.

Also, in Mongo, we use naming conventions as collections instead of table and _id instead of id.

Movable

Thank you so much but i don't where should i put it ? into the schema? – Adrian

No it’s a query. – Movable

Should i use aggregate instead find ? – Adrian

‘COLLECTIONNAME.aggregate()’ that’s all – Movable

You can add sorting in aggregate query. Aggregate is replacement to find – Movable

ok do you mean that inside the aggregate I can put sort, limit , skip and popuplate ? – Adrian

Yes you can put sort, limit and skip and you can not use populate. – Movable

First, use my query and see if you are getting your results and then you can add more operations. – Movable

thank you so much it's not easy :-( I've update my code on the question – Adrian

So my answer wasn't any useful? – Movable

your answer is amazing !!! I really appreciate, I've just put my code on the question so you can see where is my error – Adrian

Okay, add few values in the code which I have asked on your question's comment section. – Movable