问题:

Is there an equivalent to LEFT JOIN query where right collection isn't exists in MongoDB?

SQL:

SELECT * FROM TableA as A LEFT JOIN TableB as B ON A.id = B.id 
WHERE B.Id IS NULL

MongoDB: ???

P.S.: My initial sketch:

db.getCollection('collA').aggregate([
    {
      $lookup:
        {
          from: "collB",
          localField: "_id",
          foreignField: "_id",
          as: "collB"
        }           
   }
   //, {$match : collB is empty}
])

回答1:

Well your edit basically has the answer. Simply $match where the array is empty:

db.getCollection('collA').aggregate([
    { "$lookup": {
      "from": "collB",
      "localField": "_id",
      "foreignField": "_id",
      "as": "collB"
    }},
   { "$match": { "collB.0": { "$exists": false } } }
])

The $exists test on the array index of 0 is the most efficient way to ask in a query "is this an array with items in it".

回答2:

Neil Lunn's solution is working, but I have another approach, because $lookup pipe does not support Shard collection in the "from" statement.

So I used to use simple java script as follows. It's simple and easy to modify. But for performance you should have proper indexes!

var mycursor = db.collA.find( {}, {_id: 0, myId:1} ) 

mycursor.forEach( function (x){ 
    var out = db.collB.count( { yourId : x.myId } )
    if ( out > 0) {
        print('The id exists! ' + x.myId); //debugging only

        //put your other query in  here....

        }
} )