MongoDB “select x from y where id in” another table

mongodb-3.0

I'm trying to understand how can I achieve this in MongoDB:

select *
from Table1
where id in
(selct id from table2)

I'm trying to aggregate but I just can't understand how JSON works:

db.Collection1.find
    (
            {"Field_from_Collection1":
                {$nin:db.Collection2.distinct("The_Field_I_want_to_compare")
                    
                }
                
            }
    ).limit(1)

I need to find all information from collection1, but only if the ID is in collection2.

The reason I'm doing this is that I need to find the documents in Collection1, that has specifics IDS, and it's 600k IDS to look for.

as a SQL Server DBA, I'm really struggling to do this.

Best Answer

You should use $lookup for subquery.

Check out the document.

There is an example from the document.

db.collection1.aggregate([
{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}
Related Question