Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MongoDB - Equivalent of LEFT JOIN where one collection isn't exists

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}
])
like image 528
Palindromer Avatar asked Jun 06 '17 20:06

Palindromer


People also ask

What is the equivalent of a join in MongoDB?

MongoDB Joins are performed by Lookup. It performs a Left Outer Join to two or more Collections. But Lookup is only permitted in Aggregate operations. This is like a pipeline that performs query, filter and group operations.

How do I join multiple collections in MongoDB?

For performing MongoDB Join two collections, you must use the $lookup operator. It is defined as a stage that executes a left outer join with another collection and aids in filtering data from joined documents. For example, if a user requires all grades from all students, then the below query can be written: Students.

Does MongoDB query support join between collections?

Much like you would use a join to combine information from different tables in a relational database, MongoDB has a $lookup operation that allows you to join information from more than one collection.

In which stage in aggregation framework document for separate collection can be combined through a left outer join operation?

sessions collection. Performs a left outer join to another collection in the same database to filter in documents from the "joined" collection for processing. Filters the document stream to allow only matching documents to pass unmodified into the next pipeline stage.


1 Answers

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".

like image 67
Neil Lunn Avatar answered Sep 28 '22 01:09

Neil Lunn