1

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.

1

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>
     }
}
0

In order to only get the entries from collection1 which are matched in collection2 without any information from collection2 attached, you can use:

db.getCollection('collection1').aggregate([
{
   $lookup:
     {
       from: 'collection2',
       localField: 'coll2_id',
       foreignField: '_id',
       as: 'coll2_data'
     }
},
{ "$unwind": "$coll2_data" },
{ "$project": {
    "coll2_data": false
    }
}
])
  • this first makes a left-outer join and adds the information from collection2 to all collection1 entries into the field coll2_data.
  • as we only expect exactly one collection2 match for collection1.coll2_id we unwind the array to a single object. This also filters all collection1 entries which do not have a match in collection2
  • lastly the project removes the collection2 data from the joined entries

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.