Mongo lookup condition: not exist

Question

We have two collections.

One is the books collection with loads of data. Like:

{
   '_id' {"$oid": "64ba6a416504f1f01d773faa"},
   'isbn': 8765434567890,
   'booktitle': 'some text',
}
{
   '_id' {"$oid": "64ba6a416504f1f01d773faa"},
   'isbn': 8765434566666,
   'booktitle': 'other book',
}

The notes collection has documents with extra info about some of the documents in the base collection. Like:

{
   '_id' {"$oid": "64ba6a416504f1f01d773faa"},
   'isbn': 8765434567890,
   'note': 'blabla',
}

So not all documents in books have a counterpart in notes. If there is, it's one-to-one.

What I try to find out is an aggregation that shows all of the books documents (with possibly additinal {match} stuff), that specifically do not have a counterpart in the notes collection.

[
   {'$match': {}},
   {'$lookup':
      {
        'from': 'notes',
        'localField': 'isbn',
        'foreignField': 'isbn',
        'as': 'related_note',
      }
   },
   {'$project':
      {
         'isbn': '$isbn',
         'title': '$title',
         'note': '-- no notes about this title',
      }
   }
]

So not a left-join, not a right-join but a: NOT(left-join)

Anybody? Thanks.

score 3 · Accepted Answer · answered Jul 24 '23 at 13:45

What I try to find out is an aggregation that shows all of the books documents (with possibly additinal {match} stuff), that specifically do not have a counterpart in the notes collection.*

Would say that your result should be LEFT JOIN but excluding intersection.

Reference: SQL joins as Venn diagram

By default, MongoDB $lookup performs as the LEFT JOIN. To exclude the documents which are under the intersection, you can filter with related_note: [] via the $match stage.

db.books.aggregate([
  //{
  //  "$match": {}
  //},
  {
    "$lookup": {
      "from": "notes",
      "localField": "isbn",
      "foreignField": "isbn",
      "as": "related_note"
    }
  },
  {
    $match: {
      related_note: []
    }
  },
  {
    "$project": {
      "isbn": "$isbn",
      "title": "$title",
      "note": "-- no notes about this title",
      
    }
  }
])

Demo @ Mongo Playground

If life only was as simple as this. Just ask for nothing and you all wishes come true. Thanks Yong Shun ;-) — Jeex, Jul 25 '23 at 16:33

Mongo lookup condition: not exist

1 Answers1